jdub12280
08-16-2010, 09:38 AM
I am back, new issue semi-related thread attached.
http://www.vbaexpress.com/forum/showthread.php?t=33593
I am trying to follow the advice of p45cal and mikerickson and greatly reduce the number of subs i have in a user form. In order to do so , i need a select case statement the variable = button clicked, the DO would be to set a range for another variable. When i step through, the activecontrol.name always refers to the multipage1. The button used was actually on the multipage1.page1. I have also tried "me.activecontrol.name" same results. I think i will also have to select case before i unload the form... but even when i text out the unload statement i cannot get the select case to evaluate the correct button.
I only have 1 case listed in the sub below.. but i will end up having approx 40 once i know that the select case is working correctly. Any thoughts as to what i am doing wrong? Or preferrable.. what i can do right?
Again, any help is greatly appreciated
-Justin
Private Sub cmdHorn1_Click()
answer = MsgBox(msgP1 & Me.ActiveControl.Caption & msgP2, vbOKCancel, "Click OK to Update")
If answer = vbCancel Then Exit Sub ' the macro ends if the user selects the CANCEL-button
Unload Me
Application.WindowState = xlMinimized
WB_SettingsOFF
On Error GoTo ResetWorkbookSettings:
If ActiveSheet.Name <> "LINKS" Then Sheets("LINKS").Select
Select Case ActiveControl.Name
Case cmdHorn1
Set LinkTable = ActiveSheet.Range("L5:U5")
End Select
'Set LinkTable = ActiveSheet.Range("L5:U5")
For Each Rng In LinkTable
Rng.Select
Application.Run "UpdateLinks"
Next Rng
Sheets("LINKS").Range("A1").Select
WB_SettingsON
Application.WindowState = xlMaximized
MsgBox msgP3 & Me.ActiveControl.Caption & msgP4, vbInformation
Exit Sub
ResetWorkbookSettings:
'Err.Clear
WB_SettingsON
Application.WindowState = xlMaximized
End Sub
http://www.vbaexpress.com/forum/showthread.php?t=33593
I am trying to follow the advice of p45cal and mikerickson and greatly reduce the number of subs i have in a user form. In order to do so , i need a select case statement the variable = button clicked, the DO would be to set a range for another variable. When i step through, the activecontrol.name always refers to the multipage1. The button used was actually on the multipage1.page1. I have also tried "me.activecontrol.name" same results. I think i will also have to select case before i unload the form... but even when i text out the unload statement i cannot get the select case to evaluate the correct button.
I only have 1 case listed in the sub below.. but i will end up having approx 40 once i know that the select case is working correctly. Any thoughts as to what i am doing wrong? Or preferrable.. what i can do right?
Again, any help is greatly appreciated
-Justin
Private Sub cmdHorn1_Click()
answer = MsgBox(msgP1 & Me.ActiveControl.Caption & msgP2, vbOKCancel, "Click OK to Update")
If answer = vbCancel Then Exit Sub ' the macro ends if the user selects the CANCEL-button
Unload Me
Application.WindowState = xlMinimized
WB_SettingsOFF
On Error GoTo ResetWorkbookSettings:
If ActiveSheet.Name <> "LINKS" Then Sheets("LINKS").Select
Select Case ActiveControl.Name
Case cmdHorn1
Set LinkTable = ActiveSheet.Range("L5:U5")
End Select
'Set LinkTable = ActiveSheet.Range("L5:U5")
For Each Rng In LinkTable
Rng.Select
Application.Run "UpdateLinks"
Next Rng
Sheets("LINKS").Range("A1").Select
WB_SettingsON
Application.WindowState = xlMaximized
MsgBox msgP3 & Me.ActiveControl.Caption & msgP4, vbInformation
Exit Sub
ResetWorkbookSettings:
'Err.Clear
WB_SettingsON
Application.WindowState = xlMaximized
End Sub