Solved: Conditional hiding of rows

[K. Georgiadis]

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]

Paste the following into a standard module, right click on the buttons and assign the macros as appropriate
Regards
MD
[VBA]
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
[/VBA]

[K. Georgiadis]

It does exactly what I was looking for, and a lot simpler than what I was attempting. Thanks!

[K. Georgiadis]

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]

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
[VBA]
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

[/VBA]

[K. Georgiadis]

very helpful. Thanks