Forum home » Delegate support and help forum » Microsoft VBA Training and help » vba courses london - Input box for selecting a worksheet
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
vba courses london - Input box for selecting a worksheet
Resolved · Low Priority · Version Standard
Reply Tue 27 Feb 2007, 17:22Delegate Simon said...
Simon has attended:
Excel VBA Intro Intermediate course
Input box for selecting a worksheet
Carlos,
I have a question regarding creating an input box to select a particular worksheet (tab).
Each month I move and copy several worsheets to a new worbook and save them as values only so that I can distribute them as reports. All bar one of the worksheets are the same for each month. For the particular worksheet that varies from month to month I want to be able to create an input box so that I can select the worksheet relevant to the particular month. Below I have included the code that I have recorded. You will notice that in the macro the worksheet that I have selected for this month is "M1". Next month I want this to be "M2" and then the month after "M3" and so forth. Do I have to declare "M1" to be a variant? Can you please help me with code?
Thanks Simon Gleeson Ph: 02077667200
Sub Create_Month_End()
'
' Create_ME Macro
' Simon Gleeson
'
Outcome = MsgBox("Are you sure you want to create the Month End Report?", vbYesNo, "Month End Report")
If (Outcome = 6) Then
Sheets(Array("Cover Page", "Commentary", "M1")).Select
Sheets("Cover Page").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets(Array("Cover Page", "Commentary", "M1", "YTD", "Global Analytics")).Select
Sheets("Global Analytics").Activate
Sheets(Array("Cover Page", "Commentary", "M1", "YTD", "Global Analytics")).Copy
ActiveSheet.Shapes("Button 1").Select
Selection.Delete
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Selection.ClearContents
Sheets("Commentary").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Selection.ClearContents
Sheets("M1").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("B:D").Select
Selection.EntireColumn.Hidden = True
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 21
ActiveWindow.SmallScroll Down:=-216
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 7
Columns("A:A").Select
Selection.ClearContents
Rows("4:7").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("YTD").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("B:D").Select
Selection.EntireColumn.Hidden = True
Columns("A:A").Select
Selection.ClearContents
Range("A2").Select
ActiveSheet.Outline.ShowLevels RowLevels:=1
Rows("4:7").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("Global Analytics").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("B:D").Select
Selection.EntireColumn.Hidden = True
Columns("A:A").Select
Selection.ClearContents
Range("A1").Select
ActiveSheet.Outline.ShowLevels RowLevels:=1
Rows("18:21").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Cover Page").Select
ActiveWorkbook.SaveAs Filename:= _
"S:\London Office\All Files\Finance\Management Accounts\SG Management Accounts.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Windows("Group GLOBAL Analytics 2007 Template 19 Feb 2007.xls").Activate
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Cover Page").Select
Range("A1").Select
End If
End Sub
For upcoming training course dates see: Pricing & availability
RE: Input box for selecting a worksheet
Hi Simon,
Sorry for the delay in getting back to you. I'm sure you'll appreciate this is not a normal straight-forward question.
Carlos has been training and is unfortunately away sick today. I have assigned your question to him, and e-mailed him the details, so it shouldn't be too long before he has a look at your code.
I'll also inform our other VBA trainers and see if they can help out.
Thanks for your patience.
Regards, Rich
Reply Thu 1 Mar 2007, 15:22Delegate Simon said...
RE: Input box for selecting a worksheet
Thanks Rich, it is greatly appreciated!!!!!
RE: Input box for selecting a worksheet
Hi Simon
Sorry for the delay.
Yes. If you are changing the sheet monthly you need to replace the "M1" value in the code with a variable (As next month it will be "M2")
eg. dim MonthName as String
Then create either an input box or a form to that asks the user to enter the name of the new monthly sheets as required. This entry gets saved in MonthName.
This inputbox/Form is activated if Outcome=6
|