Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Wellington, New Zealand
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select sheet via Option Button (XP / SP2)

    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.

  2. #2
    Star Lounger
    Join Date
    Nov 2001
    Location
    Wellington, New Zealand
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select sheet via Option Button (XP / SP2)

    Edited by HansV to reduce huge screenshot in size (it caused horizontal scrolling). Please don't post images over 640x480 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?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select sheet via Option Button (XP / SP2)

    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

  4. #4
    Star Lounger
    Join Date
    Nov 2001
    Location
    Wellington, New Zealand
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select sheet via Option Button (XP / SP2)

    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

  5. #5
    Star Lounger
    Join Date
    Nov 2001
    Location
    Wellington, New Zealand
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select sheet via Option Button (XP / SP2)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select sheet via Option Button (XP / SP2)

    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

  7. #7
    Star Lounger
    Join Date
    Nov 2001
    Location
    Wellington, New Zealand
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select sheet via Option Button (XP / SP2)

    Brilliant work Hans - worked a treat.

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

    Many thanks
    David

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •