Solved

Copy Excel worksheet with Option Buttons and keep independent

Posted on 2011-03-25
21
283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

My experience with Windows 10 over a one year period and suggestions for smooth operation
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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…

751 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