Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
RE Any ideas why this simple proc. won't work?
Bob,
Thanks so much. Works great! One follow up if I may.....Rather than using the Worksheet names "Sheet1" or "Sheet2" (or whatever they are named), can I use the VBA name of the sheets as defined is the VBA Properties window? My users are notorious for changing the sheet names! Thanks! Steph, Try Public Shtarray Public sht As Worksheet Sub ShowAnnually2003() Shtarray = Array("Sheet1", "Sheet2") For Each sht In Worksheets(Shtarray) sht.Columns("A:D").EntireColumn.Hidden = True Next sht End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steph" wrote in message m... I get a Compile Error: Invalid Outside Procedure error, and the debugger highlights the sheet name in the Shtarray "Dom P&L Ship". I'm obviously screwing up syntax somewhere! Thanks! Public Shtarray As Sheets Public sht As Worksheet Shtarray = Array("Dom P&L Ship", "Cons P&L Ship") Sub ShowAnnually2003() For Each sht In Shtarray Workbook.Sheet(sht).Columns("A:D").EntireColumn.Hi dden = True Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
RE Any ideas why this simple proc. won't work?
Steph,
Here is one way, Public Shtarray Sub ShowAnnually2003() Dim i As Long Dim dicSheets As Object Set dicSheets = CreateObject("Scripting.Dictionary") dicSheets.Add "A1", "A1" dicSheets.Add "A2", "A2" For Each sht In ActiveWorkbook.Worksheets If dicSheets.exists(sht.CodeName) Then sht.Columns("A:D").EntireColumn.Hidden = True End If Next sht End Sub The items added to the dictionary are the sheet codenames. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steph" wrote in message om... Bob, Thanks so much. Works great! One follow up if I may.....Rather than using the Worksheet names "Sheet1" or "Sheet2" (or whatever they are named), can I use the VBA name of the sheets as defined is the VBA Properties window? My users are notorious for changing the sheet names! Thanks! Steph, Try Public Shtarray Public sht As Worksheet Sub ShowAnnually2003() Shtarray = Array("Sheet1", "Sheet2") For Each sht In Worksheets(Shtarray) sht.Columns("A:D").EntireColumn.Hidden = True Next sht End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steph" wrote in message m... I get a Compile Error: Invalid Outside Procedure error, and the debugger highlights the sheet name in the Shtarray "Dom P&L Ship". I'm obviously screwing up syntax somewhere! Thanks! Public Shtarray As Sheets Public sht As Worksheet Shtarray = Array("Dom P&L Ship", "Cons P&L Ship") Sub ShowAnnually2003() For Each sht In Shtarray Workbook.Sheet(sht).Columns("A:D").EntireColumn.Hi dden = True Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
RE Any ideas why this simple proc. won't work?
Yes. Check out the CodeName property.
-- George Nicholson Remove 'Junk' from return address. "Steph" wrote in message om... Bob, Thanks so much. Works great! One follow up if I may.....Rather than using the Worksheet names "Sheet1" or "Sheet2" (or whatever they are named), can I use the VBA name of the sheets as defined is the VBA Properties window? My users are notorious for changing the sheet names! Thanks! Steph, Try Public Shtarray Public sht As Worksheet Sub ShowAnnually2003() Shtarray = Array("Sheet1", "Sheet2") For Each sht In Worksheets(Shtarray) sht.Columns("A:D").EntireColumn.Hidden = True Next sht End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steph" wrote in message m... I get a Compile Error: Invalid Outside Procedure error, and the debugger highlights the sheet name in the Shtarray "Dom P&L Ship". I'm obviously screwing up syntax somewhere! Thanks! Public Shtarray As Sheets Public sht As Worksheet Shtarray = Array("Dom P&L Ship", "Cons P&L Ship") Sub ShowAnnually2003() For Each sht In Shtarray Workbook.Sheet(sht).Columns("A:D").EntireColumn.Hi dden = True Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup won't work, any other ideas? | Excel Worksheet Functions | |||
Assign Macro to button in Excel doesnt work Any ideas? | Excel Discussion (Misc queries) | |||
formula from Excel97 doesn't work in Excel2003,any ideas why? | Excel Worksheet Functions | |||
Any ideas why this simple proc. won't work? | Excel Programming | |||
Any ideas... Doesn't work... | Excel Programming |