Workbook_Open to fast?

[crazyeyeschase]

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

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

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.

 

[mrmmickle1]

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:

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

 

[crazyeyeschase]

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:

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

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

 

[mrmmickle1]

Glad to help out!