PDA

View Full Version : Solved: Running VBA in one worksheet to hide rows in another worksheet



simon
04-28-2006, 07:24 AM
Hi,

Am currently running the following code successfully, which hides or unhides rows in a worksheet depending on the Yes/No choice an end user makes from within a combo box called 'ComboMobileEmailAlready' which is one the same worksheet as the rows being hidden.

But I can't seem to extend the code to hide/unhide rows in a different worksheet based on this same combobox. Any ideas? I just know it's going to be something really really simple I am doing wrong - but I just can't see it. Am pulling my hair out on this! :banghead: Thanks for any help.


Code that is working (from within the worksheet 'Input'):

Private Sub ComboMobileEmailAlready_change()

'Define variable called Choice
Dim Choice

'Assigns value to the variable Choice depending on the value the
'user has just selected in the combobox.
If Sheets("ROI Calculation 2").Range("H156") = "Yes" Then Choice = "Yes"
If Sheets("ROI Calculation 2").Range("H156") = "No" Then Choice = "No"

Select Case Choice
'If you select Yes, then the rows are selected and 'unhidden' in case
'No had been previously selected and the rows were hidden - see next case.

Case "Yes"

Input sheet
Sheets("Input").Select
Rows("12:21").Select
Selection.EntireRow.Hidden = False
Range("D10").Select

[snip]


Code that is not working

Same code as above, but I am trying to add an additional set of rows to hide / unhide in a different worksheet 'Output' using:

Worksheets("Output").Activate
Rows("15:20").Select
Selection.EntireRow.Hidden = False
Rows("30:35").Select
Selection.EntireRow.Hidden = False

Using Excel 2003 Pro Edition on Win XP Pro.

Jacob Hilderbrand
04-28-2006, 07:53 AM
Well there is no need to select. Try this:

Sheets("Output").Range("15:20,30:35").EntireRow.Hidden=True

simon
04-28-2006, 08:26 AM
Worked perfectly - thank you very much. Just the kind of start to a public holiday weekend I needed! :beerchug:

Out of interest, why would selecting the appropriate sheet first prevent it from working?

Cheers,

Simon