Change Labels in Userform based on selection in ComboBox

John

Why do you have this code in the form's Activate event?

It would be better placed in the combobox's change event.

Speaking of the combobox, how exactly did you populate it?

Do you have a list of the departments somewhere?

If you do does that list correspond to the data on Sheet8?

For example if you select the first department in the combobox will the data for it be in the first column of Sheet8?

Perhaps you could attach the file?

John

When do you want the labels to change?

If it's when a different deparment is selected from the combobox then the code should go in it's change event.

Something like this perhaps.

Private Sub ComboBox1_Change()
Dim idx As Long
Dim I As Long

    idx = Combobox1.ListIndex

    If idx<>-1 Then
         With Sheet8
               For I = 3 To .Range("A" & Rows.Count).Offset(,idx).End(xlUp).Row
                     Me.Controls("Label" & I-2).Caption = .Range("A" & I).Offset(0,idx).Text
               Next I
         End With
    End If

End Sub

Norie,

Thank you. This is almost working, but I keep getting a "Cannot find the specified Object" error in the Me.Controls line.
All my labels are named by number, Label1, Label2, etc. May that be the issue?
When I change the "Label" to "Label1" (in the code you'd sent me), I still get the same error but when I move my mouse over the line of code, I can see the correct result for the first Label.

Also, the headers (Department Names) in Sheet8 are in row 2. The list of tasks starts in row 3.

John

John

I assumed the labels were named Label1, Label2 etc and that's what I based part of the code on.

It might be worth double checking the names.

Also, in your example the data you were using for the label captions appears to start in row 3, so that's where I've started.

Should the code include row 2?

John

I've assumed your labels are named Label1, Label2 etc not Label15, Label16 etc.

Try this.

Me.Controls("Label" & I+12).Caption = .Range("A" & I).Offset(0,idx).Text

PS Can you use code tags when posting code? There's a link in my signature that should explain how to use them.

I don't see why that would happen, the same labels are used for each department.

You know what would make this a whole lot easier - a sample workbook.

Then, hopefullly, there'll be no need to guess things.

Apparently Excel Forum was off-line for a few hours. Hereby attached a greatly condensed version of my workbook (deleted about 15 sheets, which interestedly enough made your code work... hmm).

Even though I followed all instructions, I can't see the attachment, so please let me know if you received it or not.

If you have the time and wherewithal, try entering some data into the user form and you'll see the error messages that appear.

btw: there are two user forms. Only use Userform1, the frmWorkTime is an old design that will be replaced by UserForm1 (once I get it working). And the links to Active sheet will change from TestType to WorkDB.

Again, thanks for all your help. Already you've been instrumental! If not a life saver

John

The attachment does appear to be there but I won't have a chance to download and look at it until later.

John

There are 3 forms, which one should I be looking at?

So the form in the workbook is working?

Does that mean this thread is solved?

Have a look at the attached file.

PS Good try at the code tags, not quite right though. Why not let the board do it for you?

Select the code and press the # button above.

Norie,

Thank you for editing the UserForm code. However I'm still running into an issue. Cannot Find the specified Object and the debugger highlights the last line in the code below (TextBox2).
When running the form it populates the name, department and type of work in the database, but not the hours. It stops right there and shows the error message.

Private Sub cmdOK3_Click()

    txtStartDate.Value = Format(Date, "mm / d / yy")
    Dim rng As Range

    Set rng = ActiveWorkbook.Sheets("TESTTYPE").Range("A" & Rows.Count).End(xlUp).Offset(1)

    For I = 1 To 15
        If Me.Controls("TextBox" & I).Value <> "" Then
            rng.Value = cboName1.Value
            rng.Offset(0, 1) = cboDept.Value
            rng.Offset(0, 2).Value = Me.Controls("Label" & I + 14).Caption
            rng.Offset(0, 3).Value = txtStartDate.Value
            rng.Offset(0, 4).Value = Me.Controls("TextBox2" & I).Value
            Set rng = rng.Offset(1)
        End If
    Next I

End Sub

John

Where exactly did the code stop?

PS Did you have a shifty at the userform (UserForm2?) with the listbox?

It appears to stop where the hours entered in the UserForm need to be applied to the database. The Name, Department and Type of work are populating the rows in the database, but then it stops. So it does recognize the hours entered in the textboxes, because it only populates rows in the database for the type of work for which I've entered a value (hours).

I hope I am showing it correctly in the code sample below. I tried to highlight the line that shows bright yellow when I choose debug on the error message (RunTime error, Could not find the specified object).
btw: the first TextBox is TextBox1, not 2 as shown in the code. However if I change that to 1, I get the same error.

UserForm2 was just a testform. The ListBox is gone (what is a shifty?). I've just copied and pasted the code we're using in UserForm1 into Userform2 (and changed all the names, references, etc.), since Userform2 will function very similar, except that it deals with Key-Indicators instead of Types of Work, and populates a different database. The form is working, except that I'm getting the exact same error message in UserForm2 as in UserForm1. See below:

Private Sub cmdOK3_Click()

Dim rng As Range

    Set rng = ActiveWorkbook.Sheets("WorkDB").Range("A" & Rows.Count).End(xlUp).Offset(1)

    For I = 1 To 15
        If Me.Controls("TextBox" & I).Value <> "" Then
          rng.Value = cboName1.Value
          rng.Offset(0, 1) = cboDept.Value
          rng.Offset(0, 2).Value = Me.Controls("Label" & I + 14).Caption
          rng.Offset(0, 3).Value = txtStartDate.Value
          rng.Offset(0, 4).Value = Me.Controls("TextBox2" & I).Value
          Set rng = rng.Offset(1)
        End If
    Next I
  
End Sub

btw: I've learned more from you in the last day and a half, then I have reading the entire VBA for Excel book. Thank you!