Workbook_Open to fast?

crazyeyeschase

Board Regular
Joined
May 6, 2014
Messages
88
Alright so I have two workbooks the first of which stores all of the settings for the second. When a user opens the first workbook they are prompted to fill in these settings and then they click a button that opens the second book.

When the second book opens it runs a Workbook_open event. This event broadcasts a message and then runs two other macros. The first macro seems to work but what it does is change some cells and their formats (style, values, and formulas) the second ,macro hides and unhides some cells depending on the users settings and this is the macro I am having issues with.

If i run it on its own is works just fine however since the worksheet is being opened from another worksheet i think its having a issue since the workbook isn't actually on screen yet.

Here are the codes for the two macros.

Macro #1
Code:
Private Sub VersConv()
Dim pwd As String
MsgBox "We are now checking your selected Version and" & _
        " adjusting the worksheet"
pwd = "*****"
With ThisWorkbook.ActiveSheet
    If Range("A8").Value = "1.8" Then
        .Unprotect Password:=pwd

            .Range("F456").Style = "data"
            .Range("E456").Formula = "=SUM(F659*4)"
            .Range("D456").Value = "415*4"

            .Range("F659").Style = "insert"
            .Range("E659").Value = "N/A"

            .Range("B58:I58").Style = "data"
            .Range("D58").Value = "19:1,263*0.1"
            .Range("E58").Formula = "=SUM(F59+(F383*0.1))"
            .Range("F58").Formula = "=SUM(E58*[Settings.xlsm]Settings!$G$8)"
            For Each Cell In Range("B12:I700")
                If Cell.Style = "1.8" Then
                    Cell.Style = "Data 1.8"
                End If
            Next
            For Each row In Range("B12:B700")
                If row.Interior.Color = RGB(112, 48, 160) Then
                    row.EntireRow.Hidden = False
                End If
            Next
        .Protect Password:=pwd
        Else
        If Range("A8").Value = "1.7" Then
            .Unprotect Password:=pwd

                .Range("F456").Style = "insert"
                .Range("E456").Value = "N/A"
                .Range("D456").Value = "N/A"

                .Range("F659").Style = "1.8"
                .Range("F659").Value = "$0.00"

                .Range("B58:I58").Style = "GM Only"
                .Range("D58").Value = "N/A"
                .Range("E58").Value = "N/A"
                .Range("F58").Value = "N/A"
            For Each Cell In Range("B12:I700")
                If Cell.Style = "Data 1.8" Then
                    Cell.Style = "1.8"
                End If
            Next
            For Each row In Range("B12:B700")
                If row.Interior.Color = RGB(112, 48, 160) Then
                    row.EntireRow.Hidden = True
                End If
            Next
            .Protect Password:=pwd
        End If
    End If
MsgBox "You have selected Version " & .Range("A8") _
        & vbNewLine & "" & vbNewLine & _
        "The worksheet is now configured for your Version."
End With
End Sub

Macro #2
Code:
Private Sub RemoveRows() 'Row Reduction
Dim row As Range
Dim pwd As String
MsgBox "Preparing to remove all non configurable rows." _
        & vbNewLine & "" & vbNewLine & _
        "This may take a moment please be patient"
pwd = "*****"
With ActiveSheet
.Unprotect Password:=pwd
If Range("A8").Value = "1.8" Then
For Each row In Range("F12:F700")
    If row.Interior.Color = RGB(255, 255, 255) Then 
    row.EntireRow.Hidden = True
        Else
        If row.Interior.Color = RGB(255, 0, 0) Then 
        row.EntireRow.Hidden = True
            Else
            If row.Interior.Color = RGB(0, 176, 240) Then 
            row.EntireRow.Hidden = True
                Else
                If row.Interior.Color = RGB(189, 215, 238) Then 
                row.EntireRow.Hidden = True
                    Else
                    If row.Interior.Color = RGB(112, 48, 160) Then
                    row.EntireRow.Hidden = True
                    End If
                End If
            End If
        End If
    End If
Next
Else
If Range("A8").Value = "1.7" Then
For Each row In Range("F12:F700")
    If row.Interior.Color = RGB(255, 255, 255) Then 
    row.EntireRow.Hidden = True
        Else
        If row.Interior.Color = RGB(255, 0, 0) Then 
        row.EntireRow.Hidden = True
            Else
            If row.Interior.Color = RGB(0, 176, 240) Then 
            row.EntireRow.Hidden = True
                Else
                If row.Interior.Color = RGB(189, 215, 238) Then 
                row.EntireRow.Hidden = True
                    Else
                    If row.Interior.Color = RGB(112, 48, 160) Then 
                    row.EntireRow.Hidden = True
                    End If
                End If
            End If
        End If
    End If
Next
If Range("A8").Value = "1.7.10" Then
For Each row In Range("FB12:B700")
    If row.Interior.Color = RGB(112, 48, 160) Then 
        row.EntireRow.Hidden = True
    End If
Next
End If
End If
End If
.Protect Password:=pwd
End With
MsgBox "Row Reduction has now completed thank you."
End Sub

Again the first macro seems to work fine however the second will work when i open the workbook by its self or run it myself. However when the workbook is being opend by another book macro 2 does not reduce the cells.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
If the issue is what you think it is.... Excel not reacting fast enough. then maybe you should insert an Application.Wait line of code in...
Here is a link to more info : CLICK HERE FOR LINK

However, If that isn't the issue the above link will not help...

When working with several open workbooks it may be possible that the code is actually running properly, but it is just not running on the workbook you intend it to....
For example that bit of code may be running on the other workbook. Make sure to select the workbook you want the code to run on:

Code:
WkBkForCodeToRun = "WorkbookNameHere"
Windows(WkBkForCodeToRun).Activate
[COLOR=#008000]'Your Code Here[/COLOR]
 

crazyeyeschase

Board Regular
Joined
May 6, 2014
Messages
88
If the issue is what you think it is.... Excel not reacting fast enough. then maybe you should insert an Application.Wait line of code in...
Here is a link to more info : CLICK HERE FOR LINK

However, If that isn't the issue the above link will not help...

When working with several open workbooks it may be possible that the code is actually running properly, but it is just not running on the workbook you intend it to....
For example that bit of code may be running on the other workbook. Make sure to select the workbook you want the code to run on:

Code:
WkBkForCodeToRun = "WorkbookNameHere"
Windows(WkBkForCodeToRun).Activate
[COLOR=#008000]'Your Code Here[/COLOR]

Thanks so much it seems as though the WkBKForCodeToRun has fixed the issue.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,334
Messages
5,601,012
Members
414,421
Latest member
tonybear1994

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top