VBA for Option buttons

Debs

Board Regular
Joined
Sep 23, 2006
Messages
222
Hi,

I'm hoping someone can help with a macro.
I have four option buttons (from forms toolbox) on a worksheet named 'Main'. When button one is clicked, it displays "Type of Service:Add" in A49 and hide rows A73 to A176 on worksheet "Customer". If another option button is clicked, then it will unhide rows A73 to A176

When button two is clicked, it displays "Type of Service: Move" in the same cell, A49 and hide rows A52 to A81 and A115 to A176 on worksheet "Customer".If another option button is clicked, then it will unhide rows A52 to A81 and A115

When button three is clicked, it displays "Type of Service: Change" in the same cell, A49 and hide rows A52 to A115 and A147to A176 on worksheet "Customer". If another option button is clicked, then it will unhide rows A52 to A115 and A147to A176.

When button four is clicked, it displays "Type of Service: Disco" in the same cell, A49 and hide rows A152 to A146 and A147 on worksheet "Customer". If another option button is clicked, then it will unhide rows A152 to A147.

Thank you, for any help given
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
if you are in design mode you should be able to double click on the button and you will see the following

sub button1_click()

end sub

then just put a variation of this code in all of them depending on what you want

Code:
sheets("Main").range("A49") = "blah"
sheets("customer").rows("1:65546").entirerow.hidden = false
sheets("customer").rows("74:176").entirerow.hidden = true
 
Upvote 0

Debs

Board Regular
Joined
Sep 23, 2006
Messages
222
The code below doesn't unhide when when another option button is selected. Is there something else I need to add?

Code:
Private Sub OptionButtonAdd_Click()

'Application.ScreenUpdating = False

Sheets("Customer").Range("A49") = "Type of Service: Add"

If OptionButtonAdd.Value = True Then
 Sheets("Customer").Range("A84:A176").EntireRow.Hidden = True
Else
  Sheets("Customer").Range("A84:A176").EntireRow.Hidden = False
   
End If
[/code]
 
Upvote 0

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
add

sheets("customer").rows("1:65546").entirerow.hidden = false

at the beginning of each sub, so it iwll unhide all rows before doing anything
 
Upvote 0

Debs

Board Regular
Joined
Sep 23, 2006
Messages
222
Thanks zzJasonzz for pointing me in the right direction. I adapted your code to fit mine and it works great!
 
Upvote 0

Forum statistics

Threads
1,191,684
Messages
5,987,993
Members
440,124
Latest member
dippy_egg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top