Hide Unhide rows and active x controls based on option buttom

[emilemil22]

Hi everyone!

I have grouped OptionButton1 and OptionButton2//// and TextBox1 and TextBox2 in sheet 1.

1. I want to actively hide rows 10:15 along with TextBox1 and TextBox2 in Sheet 1 whenever OptionButton1 is selected.
2. Additionally I want to actively unhide rows 10:15 along with TextBox1 and TextBox2 in Sheet 1 whenever OptionButton2 is selected.

Could someone advise on Macro?

PS my workbook has multiple sheets

Many thanks, for your help.

 

[Kenneth Hobson]

Right click the sheet(s) tab, View Code, and paste:

Private Sub OptionButton1_Click()
    Rows("10:15").Hidden = True
    TextBox1.Visible = True
    TextBox2.Visible = True
End Sub

Private Sub OptionButton2_Click()
    Rows("10:15").Hidden = False
    TextBox1.Visible = False
    TextBox2.Visible = False
End Sub

 

[emilemil22]

Thank you very much this works perfectly. I am able to hide unhide everything as mentioned, however, after I click optionbutton2 and textbox1 and textbox2 become again visible, I cannot write anything in them. Is this problem related to hide/unhide or it is something else?

Many thanks or your help!

 

[Jaafar Tribak]

Thank you very much this works perfectly. I am able to hide unhide everything as mentioned, however, after I click optionbutton2 and textbox1 and textbox2 become again visible, I cannot write anything in them. Is this problem related to hide/unhide or it is something else?

Many thanks or your help!

Are you using ActiveX Textboxes ? If so, they are extremely buggy and when making them visible again, they might change in size, location and editing attributes.

Can you use shape textboxes instead ?

EDIT:
Just read in the thread title that you are in fact using ActiveX textboxes .

 

[emilemil22]

Thanks for your reply.

I am have huge form so changing from activex to shapes will mean a massive manual work. Is there any way to fix the issue while using activex?

 

[Kenneth Hobson]

I had the problem once. After toggling and entering text, it worked ok.

I used the form group control and activex for the others. If you want to play with mine see: https://www.dropbox.com/s/1m3drvcqca5dt7l/ToggleHiddenRowsAndControls.xlsm?dl=0

 

[emilemil22]

Could you please write in a more detail how do you do that? as i am new to macros/controls etc.

Thank you!

 

[Kenneth Hobson]

Do what, add controls? It seems like you already know? If not, there are many tutorials. Here are a few:

1. The Developer ribbon is needed to develop. The Insert button has the form and activex controls. This link explains adding Developer and form controls.
https://support.office.com/en-us/ar...in-excel-e7e33c0c-f080-4443-b565-d21b1bdbcf43

2. Overview of both types of controls. https://support.office.com/en-us/ar...orksheet-15ba7e28-8d7f-42ab-9470-ffb9ab94e7c2

As for coding, with Design Mode in Developer active, double click the activex controls. From there, one selects the event(s) to add if more than Click event is needed. The controls are basically like those in a userform. You double click those to add events and code for each. Properties are viewed by F4 or in VBE > View > Properties.

 

[emilemil22]

I know the controles activex etc. I just don't understand how did you make the group toggel and what you mean by saying 'After toggling and entering text, it worked ok. I used the form group control and activex for the others'

Could you advise on above?

 

[Kenneth Hobson]

Toggle means clicking the option buttons to "toggle" the visibility of the textbox controls and range.