Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Copy Excel worksheet with Option Buttons and keep independent

Posted on 2011-03-25
21
Medium Priority
?
300 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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
 
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

885 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