Move macros without errors

Nitehawkhp

New Member
Joined
Dec 2, 2016
Messages
37
Hello friends,

I have looked for an answer to my question but have not been successful. Therefore, I am asking the experts here.

Let me explain what the VBA window looks like. The project explorer shows:

AddRemovePlayerForm
PlayerSelectForm
Sheet1(Setup)
Sheet2(8 Teams)
Sheet3(8 Teams Score)
Sheet4(8 Teams)
Sheet5(8 Teams Score)
Sheet6(Players)
Sheet7(Annual Scores)
Sheet8(High Scores)
Sheet9(Champions)
ThisWorkBook

There are Windows Titled:
PlayerSelectForm
This window contains most of the macros used for the Workbook

AddRemovePlayerForm
Code:
Private Sub ExitButton_Click()
     Unload AddRemovePlayerForm
End Sub

Sheet6 (Code)
Code:
[COLOR=#0000cd]Private Sub SelectPlayerForm_Click()[/COLOR]
'
' Turn off:
'       Screen updating
'       Calculation


'
' Housekeeping
'


    Range("Housekeeping").Select
    With Selection.Font
        .Color = -65536
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16764159
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = True


[COLOR=#0000cd]Many more lines of code
End Sub[/COLOR]

ThisWorkbook (Code)
Code:
[COLOR=#0000cd]Private Sub Workbook_Open()[/COLOR]
'
' Turn off:
'       Screen updating
'       Calculation


'
' Housekeeping
'
    Range("T8:X10").Select
    With Selection.Font
        .Color = -65536
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16764159
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
'
' End Housekeeping Code
'
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual


'
' Show events so user knows computer is running
'
    Application.EnableEvents = False
    
    Sheets("Setup").Select
    Call ClearNames
    Sheets("Players").Select
    Range("B2:B17").Select
    Selection.ClearContents
    Range("A1").Select
    
'
' Close Housekeeping
'


    Range("T8:X10").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16750899
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .Color = -26317
        .TintAndShade = 0
    End With
    Application.Goto Reference:="R2C1"


'
' End Housekeeping Code
'


' Turn on:
'       Screen updating
'       Calculation


    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True


'
' Show setup form
'
    
    PlayerSelectForm.Show vbModeless


[COLOR=#0000CD]End Sub[/COLOR]

I would like to reduce the number of areas where macros are located, i.e. under ThisWorkbook (Code), Sheet6 (Code), AddRemovePlayerForm.
I have tried to move these macros to the PlayerSelectForm, but if I move them, then when they are called from another macro or a BUTTON located on one of the sheets located in the Workbook.
There were other macros I was able to move to the PlayerSelectForm and they work just fine, but others will cause a error when they are called.

Here’s my question. Can these other macros be moved? That would allow me to have only one location to find macros and make changes.

Thanks for any help you can provide.

Rod
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
Macros located in Thisworkbook and Sheet modules are usually Event based Subroutines that need stay there. As a general rule, a SUB name with PRIVATE in front of it probably should remain there.

In the future, can you please wrap your code in between code tags. It's easier to read.

Jeff
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
Hi Rod,

After you paste the code into the thread editor, highlight the code and click the # icon. The font in this scroll-able tool is fixed width and the indents remain.

Jeff
 

Watch MrExcel Video

Forum statistics

Threads
1,109,027
Messages
5,526,329
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top