PDA

View Full Version : Solved: Conditional hiding of rows



K. Georgiadis
03-28-2006, 02:35 PM
In the attached zipped Excel file I'm trying to accomplish the following:

If the option button "auto calculate" is selected, hide rows 19 and 46
If option button "manual entry"is selected, hide rows 18 & 45.

Right now the link cell is G4. Depending on the value displayed on G4, the calculations that follow will either use data from 18+45 or from 19+46.

I have tried to adapt code previously provided by Firefytr for a different exercise, but I can't get it to work so perhaps I'm better off starting from scratch.

Thanks for your help

mdmackillop
03-28-2006, 03:18 PM
Paste the following into a standard module, right click on the buttons and assign the macros as appropriate
Regards
MD

Sub Manual_Click()
Range("19:19,46:46").EntireRow.Hidden = True
Range("18:18,45:45").EntireRow.Hidden = False
End Sub

Sub Auto_Click()
Range("19:19,46:46").EntireRow.Hidden = False
Range("18:18,45:45").EntireRow.Hidden = True
End Sub

K. Georgiadis
03-28-2006, 06:13 PM
It does exactly what I was looking for, and a lot simpler than what I was attempting. Thanks!

K. Georgiadis
03-28-2006, 06:50 PM
I got a little more ambitious and wanted to add another pair of option boxes below the existing ones to control two new rows (22 and 50) for manual pricing as an alternative to the current auto pricing.

The first problem I encountered is that, when I add two more option boxes, Excel makes its own assumptions about the linked cells and mixes the first two with the second two. Is there a workaround for this or is there a better alternative to a second pair of option boxes? Do I need to create two option groups?

Update: Ignore!!! I solved the jumbled linked cells by created two separate option groups

mdmackillop
03-28-2006, 11:55 PM
You can always add a line into the code to set the value of any cell rather than or in addition to using the linked cell method.

Just for info, you could work with one button by initially hiding one set of rows manually and toggling between them

Sub Toggle_Click()
Range("19:19,46:46").EntireRow.Hidden = Not (Range("19:19,46:46").EntireRow.Hidden)
Range("18:18,45:45").EntireRow.Hidden = Not (Range("18:18,45:45").EntireRow.Hidden)
End Sub

K. Georgiadis
03-29-2006, 06:13 AM
very helpful. Thanks