Solved

Copy Excel worksheet with Option Buttons and keep independent

Posted on 2011-03-25
21
271 Views
Last Modified: 2012-05-11
I need to copy a worksheet with lots of option buttons and command buttons to the same workbook.  However, when I do this now, the option buttons on the new worksheet have the same names as the first worksheet and if I select one, the option buttons on the first worksheet clear.  I want the 2nd worksheet to be independent of the first without having to recreate all of the option buttons and command buttons.
0
Comment
Question by:NFMC
  • 10
  • 9
  • 2
21 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35218364
Three things

1) What kind of controls are these? Form Controls or ActiveX controls?
2) How are you copying the worksheet?
3) may I see a sample?

Sid
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35219710
NFMC, please at least post the code here, so we can figure out a way in order to have your button always clear the option buttons of its own sheet instead of the original.

Fernando
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35219833
I created a workbook with your scenario, and it didn't work either... Sid, see in the attached workbook, for reference.

Note 1: I used button and option buttons from the Control Toolbox.
Note 2: I used Ctrl+Drag the sheet to copy the sheet to within the same workbook. I also tried with right click, Move or Copy. Additionally, I tested in both Excel 2003 and 2010.

But the description given by NFMC doesn't match what I got. Please NFMC, can you verify that you got the same behavior as I did ?

What I get is something like, it seems that Excel when you create a copy of the sheet, it leaves all the buttons from all the sheets in the same 'group', even though they are in different sheets. And by selecting an option button on the second sheet for example, the ll selection buttons from all the sheets become unselected, because that's the idea of an option button, you can select only one from that group.

I tried for a couple of hours to work around the issue, and i couldn't. Well, anyway, if this is what you're getting NFMC, I hope Sid can help you. I am anxious to see the work around for it. If not, then we'll have to see a sample of your file anyway.

Fernando
Copying-Sheet-with-Button-and-Op.xls
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35221028
Fernando, I'll wait for the OP's sample workbook. No point in playing the guessing game :)

Sid
0
 

Author Comment

by:NFMC
ID: 35227014
OK, here is my sample for you to look at.  thanks for working on this.  Please let me know if you still don't understand something. EE-sample-2.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35307173
Hmmm, how did I miss this...

Ok here is the explanation why this happens. You are using activex option buttons. When you copy option buttons they are considered to be part of the same group and hence if you select any one the rest will deselect automatically. Unfortunately there is no way you can escape from this other that placing the option button inside a Frame. See the file attached.

Sid


EE-sample-2.xlsm
0
 

Author Comment

by:NFMC
ID: 35336955
Sid, I'm still going over your document.
I will get back to ya'll on Monday.
0
 

Author Comment

by:NFMC
ID: 35392750
Sid, that Frame stuff doesn't work.
I keep getting errors when trying to click on any of the buttons.
Without the frame, the only problem that I have is that When I click on the Disable button, it only disables the Reset button and not the Option Buttons, like my code tells it to.

++++++++++++++++++++++++++++
Private Sub Reset_Click()
OptionButton1.Value = False
OptionButton2.Value = False
Range("B19").Value = 0
End Sub

Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then Range("B19").Value = "Hello World!"
End Sub

Private Sub OptionButton2_Click()
If OptionButton2.Value = True Then Range("B19").Value = "Goodbye World"
End Sub

Private Sub Disable_Click()
OptionButton1.Enabled = False
OptionButton2.Enabled = False
Reset.Enabled = False
End Sub

Private Sub Enable_Click()
OptionButton1.Enabled = True
OptionButton2.Enabled = True
Reset.Enabled = True
End Sub
+++++++++++++++++++++++++++++++
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35404980
>>>I keep getting errors when trying to click on any of the buttons.

What errors are you getting? I rechecked it and it is working just fine.

Sid
0
 

Author Comment

by:NFMC
ID: 35416997
see attached ERROR
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35417036
What happens when you click on Debug?

Sid
0
 

Author Comment

by:NFMC
ID: 35417567
see attached
EE-error.bmp
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35417810
Sorry, Had stepped out for a moment.

I see what you mean. I have made some changes to the code. I have also inserted a new button which you need to click after selecting an option.

Sample File Attached. Does it work now?

Sid

Private Sub CommandButton1_Click()
    Dim objControl As Control
    For Each objControl In Frame1.Controls
        If TypeOf objControl Is MSForms.OptionButton Then
            If objControl.Value = True Then
                Range("B19").Value = "Hello World!"
            ElseIf objControl.Value = False Then
                Range("B19").Value = "Goodbye World"
            End If
        End If
    Next
End Sub

Private Sub Reset_Click()
    Dim objControl As Control
    For Each objControl In Frame1.Controls
        If TypeOf objControl Is MSForms.OptionButton Then
            objControl.Value = False
            objControl.Value = False
        End If
    Next
    Range("B19").Value = 0
End Sub

Private Sub Disable_Click()
    Frame1.Enabled = False
    Reset.Enabled = False
End Sub

Private Sub Enable_Click()
    Frame1.Enabled = True
    Reset.Enabled = True
End Sub

Open in new window

Updated-EE-sample-2.xlsm
0
 

Author Comment

by:NFMC
ID: 35418724
That's good stuff.
Thanks Sid.  Just one more question.
How do I create the frame object with the Radio Buttons?
I'm looking under the Developer tab, and I don't see it.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35418760
Gr8.

>>>>How do I create the frame object with the Radio Buttons?
I'm looking under the Developer tab, and I don't see it.

Hope he screenshot explains it more than words :)

Sid
Untitled.jpg
0
 

Author Comment

by:NFMC
ID: 35434977
Sid, I have another question.
I have 4 radio buttons, not just 2.
How do I implement them all to change cell B19, depending on which one is selected.
i.e.
+++++++
Private Sub Update1_Click()
    Dim objControl As Control
    For Each objControl In Frame1.Controls
        If TypeOf objControl Is MSForms.OptionButton Then
            If objControl.Value = True Then
                Range("B19").Value = "Hello World!"
            ElseIf objControl.Value = False Then
                Range("B19").Value = "Goodbye World"
            ElseIf objControl.Value = False Then
                Range("B19").Value = "Later World"
            ElseIf objControl.Value = False Then
                Range("B19").Value = "Cya World"
            End If
        End If
    Next
End Sub
+++++++
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35435081
Try this. Replace the optionbutton names by relevant names.

Private Sub CommandButton1_Click()
    Dim objControl As Control
    For Each objControl In Frame1.Controls
        If TypeOf objControl Is MSForms.OptionButton Then
            If objControl.Name = "OptionButton1" And objControl.Value = True Then
                Range("B19").Value = "Hello World!"
            ElseIf objControl.Name = "OptionButton2" And objControl.Value = True Then
                Range("B19").Value = "Goodbye World"
            ElseIf objControl.Name = "OptionButton3" And objControl.Value = True Then
                Range("B19").Value = "Later World"
            ElseIf objControl.Name = "OptionButton4" And objControl.Value = True Then
                Range("B19").Value = "Cya World"
            End If
        End If
    Next
End Sub

Open in new window


Sid
0
 

Author Comment

by:NFMC
ID: 35441495
Thanks again.  I almost had that, but was missing a little bit there.
Is there an easy way to make the selection of the radio buttons change the cell value instead of having to hit the Update button after selecting the radio button?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35441509
Unfortunately there is no click event for those radio buttons in a form :)

Sid
0
 

Author Comment

by:NFMC
ID: 35441533
That's okay.  It isn't necessary.
Just trying to think of the end user.
Thanks again for the help.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35441543
You are welcome :)

Sid
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now