Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Run a macro on multiple sheets

  Asked By: Tye    Date: Mar 04    Category: MS Office    Views: 1802
  

I have 3 worksheets, Sheet1, 2 and 3. The rows from 9 to 60 are hidden
on Sheets 2 and 3. Is there any way I can enter a number in cell B17
on Sheet 1 and have that number of rows on Sheets 2 and 3 unhide at
the same time.

I know how to unhide rows and I am using an inputbox on both sheets 2
and 3. I have tried to get my macro to work on both sheets at the same
time, using sheet arrays but no luck.

I would rather have the rows unhide by entering a number into a cell
on sheet 1 if possible.

Thanks in advance for any help.

I am using Windows XP Home edition and MS Office Pro 2000.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Glenda Frazier     Answered On: Mar 04


Here is how I think you can solve your problem:
Open the Workbook. Select sheet  1. Click View on the menu bar,
Toolbars, Visual Basic. After toolbars shows, click Visual Basic
Editor icon. In the VBA window on the left side select Sheet 1 and
click View Code icon. In the Code window edit or paste the following
code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Sheets("Foaie2").Rows("9:60").Hidden = False
Sheets("Foaie3").Rows("9:60").Hidden = False

End Sub

Or, in the Sheet 1 in cell  A1 (or in other which is not used)
enter the formula: =B17 and in the VBA code window edit the
following code:

Private Sub Worksheet_Calculate()

Sheets("Foaie2").Rows("9:60").Hidden = False
Sheets("Foaie3").Rows("9:60").Hidden = False
End Sub

This code runs properly, but depending on what else exist on the
Sheet 1, a few other conditions (If…Then…End) must be added, in
order that these instructions run  only when you want (when you enter
number  in cell B17) and not at other operations in the Sheet 1.
I'll be glad to hear that it worked and in case you need to edit the
conditions (If…Then…End) send a detailed mail on the following
address: dorelbaiu@....

 
Didn't find what you were looking for? Find more on Run a macro on multiple sheets Or get search suggestion and latest updates.




Tagged: