Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 21st, 2007, 12:38 PM
Authorized User
 
Join Date: Jan 2007
Location: Hasselager, -, Denmark.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default OptionButtons as workbook sheet selector

Hi
Hope one of you out there can help me.

I have a workbook with 2 sheets. The sheet tab is hidden.

Each sheet has an identical set of OptionButtons (4 optionsbuttons, 2 per sheet).

What i want to be able to do:

jump forth and back between sheets via Optionbuttons.

Sheet1: optionbutton1(sheet1)checked. OptionButton2(sheet2)
Click OptionButton2
move to sheet2
Sheet2.optionbutton1(sheet1). OptionButton2(sheet2)checked.
set optionbutton1 in sheet1 to checked
Click OptionButton1 in sheet2
move back to sheet1.
set optionbutton2 in sheet2 to checked


Frank

Reply With Quote
  #2 (permalink)  
Old February 22nd, 2007, 04:57 PM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Not sure why you'd hide the tabs and have option buttons control it but here goes.

For sheet1 the code for option button 2 will be:

---------------------------------------------------
Private Sub OptionButton2_Click()

  Worksheets("Sheet2").OptionButton1.Value = False
  Worksheets("Sheet2").OptionButton2.Value = True
  Worksheets("Sheet2").Activate

End Sub
---------------------------------------------------

No code is needed for option button 1 on sheet 1.

For sheet 2 the code for option button 1 will be:

---------------------------------------------------
Private Sub OptionButton1_Click()

  Worksheets("Source").OptionButton1.Value = True
  Worksheets("Source").OptionButton2.Value = False
  Worksheets("Source").Activate

End Sub
---------------------------------------------------

This should produce your desired results.

If you want to be thorough and make sure the checks are checked properly on each form then just change all 4 option buttons explicitly in each routine instead of just the two on the opposite page.

Hope this is what you needed.

Reply With Quote
  #3 (permalink)  
Old February 23rd, 2007, 10:02 AM
Authorized User
 
Join Date: Jan 2007
Location: Hasselager, -, Denmark.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Allenm

Thanks for your help, but it doesn't seem to work.Does it work for you?

I can follow your idea, but i get a " Run-time error'438' Objekt doesn't support this property or method".

Worksheets("Sheet2").OptionButton1.Value = False is marked

You wondered what it's all about. I am building a program for the very novice excel user (more than i am). I want a clean user interface with almost everything hidden. I could use a commandbutton for the purpose, but now i have set my mind for optionbuttons!

Frank

Reply With Quote
  #4 (permalink)  
Old February 23rd, 2007, 11:59 AM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Hmm... Make sure the option buttons and sheet tabs are named correctly.

For instance if optionbutton1 is renamed in properties to Sheet1Option1 then it would be worksheets("<Sheet tab name here>").Sheet1Option1.value

Also, in my above example my second worksheet says worksheets("Source") because that is how I commonly name my spreadsheets. Rename worksheets("Source") to worksheets("Sheet1").

The value in "" for Worksheets() is the name of the tab.
P.S. It works fine in a worksheet I've created here today.

If there is further problems I will code it up and provide step by step instructions for you.
Reply With Quote
  #5 (permalink)  
Old February 23rd, 2007, 12:11 PM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Let me clarify:
1) Create a workbook that has sheet1 and sheet2 for two of the tab names.
2) Create two option buttons on each sheet and make sure that they are named OptionButton1 and OptionButton2 on both of them
3) In design mode double click OptionButton2 from sheet1 and insert the code for OptionButton2, N0 code for OPTIONBUTTON1 on sheet1
4) In design mode double click OptionButton1 from sheet2 and insert the code for OptionButton1, N0 code for OPTIONBUTTON2 on sheet2


---------------------------------------------------
Private Sub OptionButton2_Click()

  Worksheets("Sheet2").OptionButton1.Value = False
  Worksheets("Sheet2").OptionButton2.Value = True
  Worksheets("Sheet2").Activate

End Sub
---------------------------------------------------

No code is needed for option button 1 on sheet 1.

For sheet 2 the code for option button 1 will be:

---------------------------------------------------
Private Sub OptionButton1_Click()

  Worksheets("Sheet1").OptionButton1.Value = True
  Worksheets("Sheet1").OptionButton2.Value = False
  Worksheets("Sheet1").Activate

End Sub
---------------------------------------------------

let me know if this works for you.

Reply With Quote
  #6 (permalink)  
Old February 24th, 2007, 01:43 PM
Authorized User
 
Join Date: Jan 2007
Location: Hasselager, -, Denmark.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Allenm

Thanks for being so persistent.

The "recipe" you provided was the one i was using.

I am using a danish version of excel. Could there be a problem in this?

Perhaps you would be so kind to send me your working workbook, so i can see where the problem lies.

My email address is frankjuel@yahoo.dk



Reply With Quote
  #7 (permalink)  
Old February 26th, 2007, 12:18 PM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Not aware of any differences between the two versions myself. May want to check security level (Tools > Macro > Security). Set it to medium and it will prompt you to verify use of macros. Wouldn't suggest low priority to anyone.

Another possible problem is that you might have set the buttons on both pages to to have the same GroupName. Make sure the GroupName property for the option buttons on page 1 is different from the GroupName property of the buttons on page 2.

Reply With Quote
  #8 (permalink)  
Old February 27th, 2007, 03:43 PM
Authorized User
 
Join Date: Jan 2007
Location: Hasselager, -, Denmark.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you,Allen
I finally found out what i was doing wrong.
I made an embarrassing newbies fault. I was using Optionbuttons from the formulas toolbox, instead of optionbuttons from the control element tool box. Now everything works fine.
Once again, thanks for your patience.
Frank

Reply With Quote
  #9 (permalink)  
Old February 27th, 2007, 04:32 PM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Happy to help.

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Open Workbook,Copy Sheet,Move Sheet, Close/Save ptrussell2009 Excel VBA 0 June 13th, 2008 02:28 PM
access function in data sheet(another sheet) jani Excel VBA 1 May 21st, 2008 07:15 PM
theme selector rav BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 3 February 11th, 2007 11:44 PM
Code to populate multiple sheet into workbook kud Excel VBA 0 June 9th, 2004 11:37 AM
Slot Selector motupally Classic ASP Basics 0 July 28th, 2003 05:03 AM



All times are GMT -4. The time now is 08:31 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.