Run Macros/Commands on different worksheets

[thomas.szwed]

Hi,

I have a fairly tricky problem. Imagine my workbook contains 5 sheets, so 'Sheet1' through to 'Sheet5'.

Sheet 1 is ALWAYS displayed and is the master sheet. It contains lots of input boxes. Sheets 2 - 5 are hidden. They display information from the master sheet.

On Sheet 1 there is a cell dropdown that contains the names of Sheets 2 - 5. When you select a sheet from there, the worksheet tab appears at the bottom along side Sheet 1.

Say i wanted to print just the sheet that the user selected, how would i code a button to do that?

How would it know which sheet to print? Would it be through some sort of link to the dropdown list options?

What i have explained above is an example to try and make you understand. Below is the code i am currently using and there are more sheets here as this is the real version ('SELECT' is the name given to my master sheet). Maybe this code may help.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Not Intersect(Target, Me.Range("K10")) Is Nothing Then

Select Case Range("K10").Value

Case "SELECT": Call SheetVisibility(False, False, False, False, False, False, False, False, False)
Case "New Malden - Manager": Call SheetVisibility(True, False, False, False, False, False, False, False, False)
Case "New Malden - Staff": Call SheetVisibility(False, True, False, False, False, False, False, False, False)
Case "Sudbury Process": Call SheetVisibility(False, False, True, False, False, False, False, False, False)
Case "Sudbury Staff/Man": Call SheetVisibility(False, False, False, True, False, False, False, False, False)
Case "Wisbech Process": Call SheetVisibility(False, False, False, False, True, False, False, False, False)
Case "Wisbech Staff/Man": Call SheetVisibility(False, False, False, False, False, True, False, False, False)
Case "Aintree Process": Call SheetVisibility(False, False, False, False, False, False, True, False, False)
Case "Aintree Staff/Man": Call SheetVisibility(False, False, False, False, False, False, False, True, False)
Case "Factory Grade 4+": Call SheetVisibility(False, False, False, False, False, False, False, False, True)
End Select

End If
End Sub


Private Function SheetVisibility(sh1 As Boolean, sh2 As Boolean, _
sh3 As Boolean, sh4 As Boolean, _
sh5 As Boolean, sh6 As Boolean, _
sh7 As Boolean, sh8 As Boolean, _
sh9 As Boolean)

Application.ScreenUpdating = False
Worksheets("NewMaldenMan").Visible = xlSheetVeryHidden
Worksheets("NewMaldenStaff").Visible = xlSheetVeryHidden
Worksheets("SudburyProcess").Visible = xlSheetVeryHidden
Worksheets("SudburyStaffMan").Visible = xlSheetVeryHidden
Worksheets("WisbechProcess").Visible = xlSheetVeryHidden
Worksheets("WisbechStaffMan").Visible = xlSheetVeryHidden
Worksheets("AintreeProcess").Visible = xlSheetVeryHidden
Worksheets("AintreeStaffMan").Visible = xlSheetVeryHidden
Worksheets("FactGrade4+").Visible = xlSheetVeryHidden
If sh1 Then Worksheets("NewMaldenMan").Visible = xlSheetVisible
If sh2 Then Worksheets("NewMaldenStaff").Visible = xlSheetVisible
If sh3 Then Worksheets("SudburyProcess").Visible = xlSheetVisible
If sh4 Then Worksheets("SudburyStaffMan").Visible = xlSheetVisible
If sh5 Then Worksheets("WisbechProcess").Visible = xlSheetVisible
If sh6 Then Worksheets("WisbechStaffMan").Visible = xlSheetVisible
If sh7 Then Worksheets("AintreeProcess").Visible = xlSheetVisible
If sh8 Then Worksheets("AintreeStaffMan").Visible = xlSheetVisible
If sh9 Then Worksheets("FactGrade4+").Visible = xlSheetVisible
Application.ScreenUpdating = True

End Function

Thanks if anyone can help me out....:friends:

[Bob Phillips]

You would just use the value in the dropdown, that tells you which sheet is visible.

[thomas.szwed]

Coul you give me an example of this print function in code pls xld?

[Bob Phillips]

Worksheets(Range(dropdown_cell_reference).Value).Print

[thomas.szwed]

Great so would it just be......

Worksheets(Range(K10).Value).Print

Can i just say, that the names in this dropdown slightly differ from the names of the worksheets, how would i get around this? be like vlooking up a value?

Thanks

[Bob Phillips]

Differ in what way?

[thomas.szwed]

Well for instance the sheet is called 'NewMaldenMan' but its called 'New Malden - Manager' in the dropdown list.....

[Bob Phillips]

A lookup list is probably best then, put the details on a worksheet and use Application.Vlookup.

[thomas.szwed]

No idea how to use application.vlookup!

Can you give me an example? say cell A1 contains the nickname 'New Malden Manager' and cell B1 contains the real sheet name 'NewMaldenMan'.

Many Thanks.

[Bob Phillips]

Well, let us assume that A1:A10 contain the long form, B1:B10 contain the short form, and K10 is the dropdown cell. I will also assume that the selection from K10 is guaranteed to be in A1:A10, so I won't add error handling



sSheetName = Application.Vlookup(Range("K10").Value, Range("A1:B10")2,False)


and then use sSheetName as the name of the sheet to work on.

[thomas.szwed]

Thanks, have tried to use the code on a function but im getting a syntax error. Can you help ?


Sub FormatAndPreview()

sSheetName = Application.Vlookup(Range("K10").Value, Range("F115:G123")2,False)

Sheets("sSheetName").Select
Cells.Select
Selection.Rows.AutoFit

Sheets("sSheetName").Select
ActiveWindow.SelectedSheets.PrintPreview

End Sub


Taa

[Bob Phillips]

I dropped a comma



Sub FormatAndPreview()

sSheetName = Application.VLookup(Range("K10").Value, Range("F115:G123"), 2, False)

Sheets("sSheetName").Select
Cells.Select
Selection.Rows.AutoFit

Sheets("sSheetName").Select
ActiveWindow.SelectedSheets.PrintPreview

End Sub

[thomas.szwed]

Getting variable not defined now?

[Bob Phillips]

You must be using Option Explicit. Good man!



Sub FormatAndPreview()
Dim sSheetName As String

sSheetName = Application.VLookup(Range("K10").Value, Range("F115:G123"), 2, False)

With Sheets(sSheetName)

.Rows.AutoFit
.PrintPreview
End With

End Sub

[thomas.szwed]

Haha, yes!

This works fine. But this cell is blank when the user first opens the sheet. IF they were to press one of my buttons before choosing an entry from this cell, then i get a runtime erorr. Is there any exemption you can make in the code to not let it run unless an actual cell selection is made?

Thanks

[Bob Phillips]

Sub FormatAndPreview()
Dim sSheetName As String

sSheetName = Application.VLookup(Range("K10").Value, Range("F115:G123"), 2, False)

With Sheets(sSheetName)

If .Value <> "" Then

.Rows.AutoFit
.PrintPreview
End If
End With

End Sub

[thomas.szwed]

I'm now getting a type mismatch?

To Clarify. The dropdown cell that holds the list of sheets is blank as default, until a selection is made by the user.

With the current code if a user clicks one of my buttons then we get runtime errors, because the codes not built to run with a no entry in the dropdown cell....

Therefore i need some sort of warning message or refusal message that wont carry out the action unless a value is selected from the dropdown in that cell.

Do you get me>? How can i do this in code?

THanks

[thomas.szwed]

Can you help XLD?