I've added a label (MyLabel) to the userform during runtime. I also used
C. Pearsons' code found here (http://www.cpearson.com/excel/vbe.htm) to add
code for a _Click Event for that label. Both the label and the code are
added correctly, but the _Click Event does not trigger.

Any thoughts? Solutions?

Mike

Private Sub MyCombo_Change()
Dim combolist
If MyCombo.ListCount = 0 Then Exit Sub
topval = 46
lb = 1
For Each wks In Worksheets
If wks.Range("A1") = MyCombo.Value Then
Set MyLabel = Controls.Add("Forms.label.1", "MyLabel" & lb)
MyLabel.Top = topval
MyLabel.Caption = wks.CodeName
AddProcedure
lb = lb + 1
topval = topval + 14
End If
Next wks
End Sub

Sub AddProcedure()
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Userform1").C odeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Sub " & MyLabel.Name & "_Click()" & Chr(13) & _
MyLabel.Caption & ".select " & Chr(13) & _
"End Sub"
End With
End Sub

NOTE: I've tried using wks.Name with "Sheet(" & MyLabel.Caption &
").select" in as well as the wks.CodeName shown above.

It didn't work for me, either.

But personally, I wouldn't use labels for this--or the codenames, either.

I put a combobox, a listbox, and a commandbutton on a userform.

This was the code I used:

Option Explicit
Dim BlkProc As Boolean
Private Sub MyCombo_Change()

Dim wks As Worksheet

BlkProc = True
Me.ListBox1.Clear
BlkProc = False

If Me.myCombo.ListIndex = -1 Then
Exit Sub
End If

For Each wks In Worksheets
If wks.Range("A1") = Me.myCombo.Value Then
Me.ListBox1.AddItem wks.Name
End If
Next wks
End Sub
Private Sub ListBox1_Change()

If BlkProc = True Then
Exit Sub
End If

With Me.ListBox1
If .ListIndex < 0 Then
Beep
Else
Worksheets(.List(.ListIndex)).Select
End If
End With

End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
With Me.myCombo
.AddItem "ASDF"
.AddItem "QWER"
.AddItem "ZXCV"
End With
With Me.myCombo
.Style = fmStyleDropDownList
End With
With Me.ListBox1
.Clear
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectSingle
End With
End Sub


I think it made the coding much easier.

crazybass2 wrote:

I've added a label (MyLabel) to the userform during runtime. I also used
C. Pearsons' code found here (http://www.cpearson.com/excel/vbe.htm) to add
code for a _Click Event for that label. Both the label and the code are
added correctly, but the _Click Event does not trigger.

Any thoughts? Solutions?

Mike

Private Sub MyCombo_Change()
Dim combolist
If MyCombo.ListCount = 0 Then Exit Sub
topval = 46
lb = 1
For Each wks In Worksheets
If wks.Range("A1") = MyCombo.Value Then
Set MyLabel = Controls.Add("Forms.label.1", "MyLabel" & lb)
MyLabel.Top = topval
MyLabel.Caption = wks.CodeName
AddProcedure
lb = lb + 1
topval = topval + 14
End If
Next wks
End Sub

Sub AddProcedure()
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Userform1").C odeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Sub " & MyLabel.Name & "_Click()" & Chr(13) & _
MyLabel.Caption & ".select " & Chr(13) & _
"End Sub"
End With
End Sub

NOTE: I've tried using wks.Name with "Sheet(" & MyLabel.Caption &
").select" in as well as the wks.CodeName shown above.

--

Dave Peterson

Dave,

Thanks for the response. I tried adding commandbuttons instead of labels,
and the results were the same. The Click event added during runtime does not
work. That is the problem I need solved.

Mike

"Dave Peterson" wrote:

It didn't work for me, either.

But personally, I wouldn't use labels for this--or the codenames, either.

I put a combobox, a listbox, and a commandbutton on a userform.

This was the code I used:

Option Explicit
Dim BlkProc As Boolean
Private Sub MyCombo_Change()

Dim wks As Worksheet

BlkProc = True
Me.ListBox1.Clear
BlkProc = False

If Me.myCombo.ListIndex = -1 Then
Exit Sub
End If

For Each wks In Worksheets
If wks.Range("A1") = Me.myCombo.Value Then
Me.ListBox1.AddItem wks.Name
End If
Next wks
End Sub
Private Sub ListBox1_Change()

If BlkProc = True Then
Exit Sub
End If

With Me.ListBox1
If .ListIndex < 0 Then
Beep
Else
Worksheets(.List(.ListIndex)).Select
End If
End With

End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
With Me.myCombo
.AddItem "ASDF"
.AddItem "QWER"
.AddItem "ZXCV"
End With
With Me.myCombo
.Style = fmStyleDropDownList
End With
With Me.ListBox1
.Clear
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectSingle
End With
End Sub


I think it made the coding much easier.

crazybass2 wrote:

I've added a label (MyLabel) to the userform during runtime. I also used
C. Pearsons' code found here (http://www.cpearson.com/excel/vbe.htm) to add
code for a _Click Event for that label. Both the label and the code are
added correctly, but the _Click Event does not trigger.

Any thoughts? Solutions?

Mike

Private Sub MyCombo_Change()
Dim combolist
If MyCombo.ListCount = 0 Then Exit Sub
topval = 46
lb = 1
For Each wks In Worksheets
If wks.Range("A1") = MyCombo.Value Then
Set MyLabel = Controls.Add("Forms.label.1", "MyLabel" & lb)
MyLabel.Top = topval
MyLabel.Caption = wks.CodeName
AddProcedure
lb = lb + 1
topval = topval + 14
End If
Next wks
End Sub

Sub AddProcedure()
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Userform1").C odeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Sub " & MyLabel.Name & "_Click()" & Chr(13) & _
MyLabel.Caption & ".select " & Chr(13) & _
"End Sub"
End With
End Sub

NOTE: I've tried using wks.Name with "Sheet(" & MyLabel.Caption &
").select" in as well as the wks.CodeName shown above.

--

Dave Peterson

Good luck.

crazybass2 wrote:

Dave,

Thanks for the response. I tried adding commandbuttons instead of labels,
and the results were the same. The Click event added during runtime does not
work. That is the problem I need solved.

Mike

"Dave Peterson" wrote:

It didn't work for me, either.

But personally, I wouldn't use labels for this--or the codenames, either.

I put a combobox, a listbox, and a commandbutton on a userform.

This was the code I used:

Option Explicit
Dim BlkProc As Boolean
Private Sub MyCombo_Change()

Dim wks As Worksheet

BlkProc = True
Me.ListBox1.Clear
BlkProc = False

If Me.myCombo.ListIndex = -1 Then
Exit Sub
End If

For Each wks In Worksheets
If wks.Range("A1") = Me.myCombo.Value Then
Me.ListBox1.AddItem wks.Name
End If
Next wks
End Sub
Private Sub ListBox1_Change()

If BlkProc = True Then
Exit Sub
End If

With Me.ListBox1
If .ListIndex < 0 Then
Beep
Else
Worksheets(.List(.ListIndex)).Select
End If
End With

End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
With Me.myCombo
.AddItem "ASDF"
.AddItem "QWER"
.AddItem "ZXCV"
End With
With Me.myCombo
.Style = fmStyleDropDownList
End With
With Me.ListBox1
.Clear
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectSingle
End With
End Sub


I think it made the coding much easier.

crazybass2 wrote:

I've added a label (MyLabel) to the userform during runtime. I also used
C. Pearsons' code found here (http://www.cpearson.com/excel/vbe.htm) to add
code for a _Click Event for that label. Both the label and the code are
added correctly, but the _Click Event does not trigger.

Any thoughts? Solutions?

Mike

Private Sub MyCombo_Change()
Dim combolist
If MyCombo.ListCount = 0 Then Exit Sub
topval = 46
lb = 1
For Each wks In Worksheets
If wks.Range("A1") = MyCombo.Value Then
Set MyLabel = Controls.Add("Forms.label.1", "MyLabel" & lb)
MyLabel.Top = topval
MyLabel.Caption = wks.CodeName
AddProcedure
lb = lb + 1
topval = topval + 14
End If
Next wks
End Sub

Sub AddProcedure()
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Userform1").C odeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Sub " & MyLabel.Name & "_Click()" & Chr(13) & _
MyLabel.Caption & ".select " & Chr(13) & _
"End Sub"
End With
End Sub

NOTE: I've tried using wks.Name with "Sheet(" & MyLabel.Caption &
").select" in as well as the wks.CodeName shown above.

--

Dave Peterson


--

Dave Peterson