Select sheet via Option Button (XP / SP2)

Topic

New Reply

Oops – sorry about the size Hans., esp since I replaced with a another large one after you had altered it – did not intend it to be so large, will generate a smaller one shortly.

Reply | Quote

Replies

Edited by HansV to reduce huge screenshot in size (it caused horizontal scrolling). Please don’t post images over 640×480 pixels in size.

Novice VBA here

Have an OptionButton group, with one OptionButton for each data sheet in a workbook. Each OptionButton has the name of the datasheet it refers to (eg. if sheets are “D1”, “D2”, “D3”, then the OptionButton names(? text) are “D1”, “D2”, “D3”. See actual example attached

Have a macro that scans the sheet names & generates the OptionButtons accordingly. Done that, works fine (via “Add Option Buttons” in example).

On selecting a particular OptionButton, I want want the assigned macro to go and get data from the same named sheet & paste it into another (“Data” in the example. The data is plotted in the chart on sheet “Plot”). ie. using the OptionButtions to change the data displayed in the chart.

Bit I am stuck on, is getting the name of the OptionButton so that the macro can select the correct sheet; eg Have tried various options such as

Sheets(ActiveSheet.OptionButtons.Name).Select

and

Sheets(Selection.Characters.Text).Select

etc to no effect. Also tried

Dim SheetName As String
SheetName = Selection.Characters.Text ‘ ie I wont this to be the name of the OptionButton that was selected
Sheets(SheetName).Select

Still no chop.

Any suggestions?

Reply | Quote

Have the option buttons been created from the Forms toolbar? If so, they are shapes with names such as Option Button 2, etc. This is not the same as the caption (the displayed text). You can find these names in the Immediate window:
– Right click an option button to select it.
– Switch to the Visual Basic Editor (Alt+F11)
– Activate the Immediate window (Ctrl+G)
– Type ? Selection.Name and press Enter.

You can assign macros to each of the option buttons with code like the following, with the appropriate names substituted:

Sub OptionButton2_Click()
If ActiveSheet.Shapes(“Option Button 2”).ControlFormat.Value = 1 Then
‘ code to be executed if Option Button 2 is “on”
…
End If
End Sub

Sub OptionButton3_Click()
If ActiveSheet.Shapes(“Option Button 3”).ControlFormat.Value = 1 Then
‘ code to be executed if Option Button 3 is “on”
…
End If
End Sub

Sub OptionButton4_Click()
If ActiveSheet.Shapes(“Option Button 4”).ControlFormat.Value = 1 Then
‘ code to be executed if Option Button 4 is “on”
…
End If
End Sub

Reply | Quote

re: “Have the option buttons been created from the Forms toolbar? … names such as Option Button 2.”

Originally yes, but creation now via macro lines

For lCount = 4 To lShtLast
lTop = lTop + lInc
ActiveSheet.OptionButtons.Add(lLeft, lTop, 150#, 14#).Select
Selection.Characters.Text = Sheets(lCount).Name
Selection.OnAction = “Macro7”
Next lCount

That works well.

“? Selection.Name” in the Immediate window yields “Option Button 72” because have created lots of buttons. That name will keep changing each time I change the data & create new buttons.

Now trying to get “Macro7” to work. Hoped to use same Macro for all OptionButtons, as could have up to 30 sheets or so (= c. 30 OptionButtons) – actual number will vary. Each sheet is a result from another (Fortran) program. Buttons created automatically via the above code after results of several analyses have been assembled into a new workbook, Hoped that I would not have to create a macro for each button as well.

Code in “Macro7” is just:

SheetName = Selection.Name ‘ This is problem line – want “SheetName” = selected option button name
Sheets(SheetName).Select
Range(“A1:I1”).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets(“Data”).Select
Range(“A1”).Select
ActiveSheet.Paste
Sheets(“Plot”).Select
Range(“A1”).Select

Guess I am needing the OptionButton caption text?

David

Reply | Quote

Further to above:

? Selection.Characters.Text (in the immediate window yields)
TF20010324_M6.8_50km

Which is what I need. But

SheetName = Selection.Characters.Text

yields “SheetName” = “” and causes the following line to fail.

Can this, or similar be made to work?

David

Reply | Quote

Perhaps you can use this:

Function GetCaption() As String
Dim opt As Object
For Each opt In ActiveSheet.OptionButtons
If opt.Value = 1 Then
GetCaption = opt.Caption
Exit For
End If
Next opt
End Function

You can then use

SheetName = GetCaption

Reply | Quote

Brilliant work Hans – worked a treat.

Would never have got there by myself, despite many attempts with Google.

Many thanks
David

Reply | Quote