Solved: Help - Select Case ActiveControl.Name

[jdub12280]

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

[VBA]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


[/VBA]

[Bob Phillips]

The name is a string, so shouldn't it be

[vba]

Case "cmdHorn1"
[/vba]

[Bob Phillips]

Oh, and to answer your question

[vba]

Select Case ActiveControl.Name
Case "MultiPage1"

Select Case Me.MultiPage1.Pages(Me.MultiPage1.Value).ActiveControl.Name

Case "cmdHorn1"

Set LinkTable = ActiveSheet.Range("L5:U5")
End Select
End Select
[/vba]

[jdub12280]

The name is a string, so shouldn't it be

[vba]

Case "cmdHorn1"
[/vba]

i had thought that as well, but i couldn't get the select case part right to test what it was setting focus on.

Also...

[vba]Select Case Me.MultiPage1.Pages(Me.MultiPage1.Value).ActiveControl.Name[/vba]

the part that is in side ( ) what does that mean or represent? i think i follow me = userform multipage1 is the object... but then lost with the .value does each button on the multipage return a different value?

In any event, thanks for the reply and help!! I will be modifying my code to match your suggestion... and reply back with the results. Thanks again.

-Justin

[jdub12280]

Thanks xld... that worked as I was needing and I understand the answer to my previous question. Hopefully I have borrowed enough of your knowledge to get me through my current endeavor

-Justin

[Bob Phillips]

Also...

[vba]Select Case Me.MultiPage1.Pages(Me.MultiPage1.Value).ActiveControl.Name[/vba]

the part that is in side ( ) what does that mean or represent? i think i follow me = userform multipage1 is the object... but then lost with the .value does each button on the multipage return a different value?

Just to be sure, the Value property of the MultiPage object refers to the page. So Me.MultiPage1.Value returns the current page, so the activecontrol on that page is picked up.

[jdub12280]

Thanks XLD while stepping through i realized thats what it was doing.. so in my case it always returned an index value of 0-3. Which also helped me figure out how to target specific multi-pages (was having difficulty with that as well).

Thanks again!

-Justin