vba courses london - VBA Excel

Resolved · Low Priority · Version Standard

Thu 30 Nov 2006, 17:00 Delegate Simon said...

Simon has attended:
Excel VBA Intro Intermediate course

VBA Excel

During the VBA course with Carlos he indicated that you could run a macro and prevent the screen from flickering (i.e while it was calculating). Can you please let me know how to do this?

Fri 1 Dec 2006, 11:14 Trainer Carlos said...

VBA Excel Stop Flickering Screen

Running VBA code may cause the screen to flicker as the monitor is the slowest part of the program and cannot keep up with the very fast changes taking place.

To switch off the screen until the program is run enter the following code line at the beginning of the procedure:

Application.ScreenUpdating = False

The screen comes on automatically on completion of the program. If you require screen updating to resume before the end type

Application.ScreenUpdating = True

Tue 27 Feb 2007, 15:41 Delegate Simon said...

RE: VBA Excel

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

Mon 5 Mar 2007, 09:51 Trainer Carlos said...

RE: VBA Excel

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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

VBA tip:

Use VbNullString instead of

When needing to default a String variable back to it's default of "" use vbNullString as in the following example:

Sub ClearText()

Dim strEmpName As String

strEmpName = "John Smith"
MsgBox strEmpName

strEmpName = vbNullString
MsgBox strEmpName

End Sub

View all VBA hints and tips