Skip a column when entering data through a Userform.

[Astroyd]

Good evening all,

I am new to Excel this will be my first question. I have enjoyed reading and searching through the Forums and comments on Chandoo. I have found it very useful.


I have a spreadsheet that is populated via a Userform. I have arrived at a problem where I don't know how to write the Code so as to Skip Column B i.e.

Column A, Column B, Column C, Column D, Column E, Column F,

Serial, ID, List of Activities, Owner Environment, Planned Start, Planned end,

1, HQ DLC0001, Campaign Plan, HQ DLC, 28-Feb-14, 18-Jul-14,

From the text Box I fill Serial, List of Activity, Owner Environment, Planned Start, Planned End.

Column B - ID - Is a unique ID that is created via a formula.

How can I add a line to the code below so that the User form skips Column B and only places the inputted data into Columns A, C, D, E and F.

Option Explicit

Dim id As Integer, i As Integer, j As Integer, flag As Boolean

Sub GetData()

Sheets("DLC MAS Template").Select

If IsNumeric(UserForm1.TextBox1.Value) Then
    flag = False
    i = 10
    id = UserForm1.TextBox1.Value

    Do While Cells(i + 1, 1).Value <> ""

        If Cells(i + 1, 1).Value = id Then
            flag = True
            For j = 2 To 5
            If j = 3 Then
                    UserForm1.Controls("combobox1").Value = Cells(i + 1, j).Value
                Else
                    UserForm1.Controls("TextBox" & j).Value = Cells(i + 1, j).Value
            End If
            Next j
        End If
    
        i = i + 1
    
    Loop

    If flag = False Then
        For j = 2 To 5
        If j = 3 Then
            UserForm1.Controls("combobox1").Value = ""
         Else
            UserForm1.Controls("TextBox" & j).Value = ""
            End If
            Next j
    End If

Else
    ClearForm
End If

End Sub

Thank you.

David

 

[Marc L]

Hi !

Smarter is to place ID in first column …
​

 

[Smallman]

Hi David

This should give you a flavour in case you come across this problem in future. Always best to post a file as others can see the layout of your problem and people like myself won't need to contemplate a userform build.

I have simplified your problem. Looks like you are running the procedure from another sheet. I have run this from another sheet and treated all the cells that meet your criteria in one hit.

You can add the code that clears the userform.

ption Explicit

Sub GetData()
Dim ws As Worksheet
Dim ar As Variant
Dim id As Integer
Dim lr As Long
Dim i As Integer

ar = [{3,5,6}]
Set ws = Sheets("DLC MAS Template")
id = ws.[A5]

ws.Range("A10", ws.Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, id
    For i = 1 To 3
        lr = ws.Range("A" & Rows.Count).End(xlUp).Row
        If lr > 10 Then ws.Range(ws.Cells(11, ar(i)), ws.Cells(lr, ar(i))).Value = ws.Cells(5, ar(i))
    Next i
ws.AutoFilterMode = 0
End Sub

I will attach a file to show how it works. Just change the cell in Yellow (ID on your USerForm) and the data in your sheet should change too.

The data in Row 5 of your main sheet is meant to represent your combo boxes. I am sure you will work it out.

Take care

Smallman

 

[Astroyd]

Good evening Smallman.

Thank you for your reply.
Apologies for the missing File upload. Not sure what I did wrong but hopefully it attaches this time.
I would like to offer my apologies for creating you the extra work.
I have attached a copy of the Spreadsheet that I am trying
to get to work.
When I click on the add new activity button an enter a
number (try any number between 1 - 20) into the first text box in the userform you will see that the data in Column B shows up in the
User form.

Column B has a formula in it that creates a unique ID number
so I don't want to over right this column. I only want to
add data to Columns A, C, D, E, F.

Hope this makes sense.

I have designed this MAS based on a Gantt chart from
Chandorg with a number of other additions. (Still a work in process)

Again thank you for you time and assistance.

David