Help with VBA code to call spreadsheets and Macros based on cell references

[willow1985]

Hello,

I have written the below code and have put in the word Cell Reference where I would like the code to refer to text in a particular cell, lets say: A3 from spreadsheet 'Criteria'.

Any help with incorporating a cell reference in the 4 mentioned places would be greatly appreciated.

Thank you very much!

Dim Msg As String, Ans As Variant, lr As Long, sh As Worksheet, lo As ListObject

  Application.ScreenUpdating = False
  If Application.CountIf(Sheets("Criteria").Range("A3:D3"), "") > 0 Then
     MsgBox "Please Complete all Fields"
    Exit Sub
  End If
Msg = "The Report for Cell Reference will be run," & vbCrLf & "" & vbCrLf & " Do you wish to continue?"

    Ans = MsgBox(Msg, vbYesNo)

    Select Case Ans

        Case vbYes

Workbooks.Open "X:\QUALITY\Quality Assurance\Cell Reference Reports for Audits.xlsx"

Windows("Cell Reference Reports for Audits.xlsx").Activate

Call Cell Reference

   Case vbNo
        GoTo Quit:
    End Select

Quit:
End Sub

 

[GWteB]

Try this ...

Sub Willow1985()


    Const cMsg      As String = "The Report for @CellRef@ will be run," & vbCrLf & "" & vbCrLf & " Do you wish to continue?"
    Const cWbFile   As String = "X:\QUALITY\Quality Assurance\@CellRef@ Reports for Audits.xlsx"

    Dim sCellText As String, sWbFile As String
    Dim Msg As String, Ans As Variant, lr As Long, sh As Worksheet, lo As ListObject

    sCellText = Sheets("Criteria").Range("A3").Value    ' << change as required

    sWbFile = Replace(cWbFile, "@CellRef@", sCellText)
    Msg = Replace(cMsg, "@CellRef@", sCellText)

    Application.ScreenUpdating = False
    If Application.CountIf(Sheets("Criteria").Range("A3:D3"), "") > 0 Then
        MsgBox "Please Complete all Fields"
        Exit Sub
    End If

    Ans = MsgBox(Msg, vbYesNo)

    Select Case Ans

        Case vbYes

            Workbooks.Open sWbFile
            ' Windows("Cell Reference Reports for Audits.xlsx").Activate  ' << superfluous

            Application.Run sCellText

    Case vbNo
        GoTo Quit:
        End Select

Quit:
End Sub

 

[willow1985]

Sorry I am unclear on a few things in your code:

1.

What about the:
Call Cell Reference
line?

I would like to call a Macro that will be named the same as the cell reference.

2.

Workbooks.Open sWbFile
' Windows("Cell Reference Reports for Audits.xlsx").Activate ' << superfluous
Application.Run sCellText

I am also not sure what this line is but I will need to toggle back and forth between 2 workbooks which is why I required the line:

Windows("@CellRef@ Reports for Audits.xlsx").Activate - would this not work?



Thank you.

 

[GWteB]

Sorry I am unclear on a few things in your code:
1. What about the: Call Cell Reference line? I would like to call a Macro that will be named the same as the cell reference.

The Application.Run method is a kind of equivalent to the Call statement, so it does exactly what you requested.
As you said in your first post ...

I would like the code to refer to text in a particular cell, lets say: A3 from spreadsheet 'Criteria'.

the value in that particular cell is assigned to the sCellText variable, which is used to
- compose the required screen message;
- compose the required path\file specification;
- call a macro with a name equal to the containings of that variable.
Needless to say the macro has to be available and its name must meet the requirements of Excel / VBA (among others no use of numeric character in front, no use of space or following characters ().!{}[]\|=+$#@&^~, name can't exceed 255 characters in length).

2. .... but I will need to toggle back and forth between 2 workbooks which is why I required the line:
Windows("@CellRef@ Reports for Audits.xlsx").Activate - would this not work?

In the context of your provided code this line is superfluous due to the first previous line, because whenever a workbook is opened, the window of that particular workbook becomes activated automatically (it gets focus), so there is no use in activating that same window (again) since it is already active. If you want to maintain that code line, there is nothing wrong with that, although a minor modification in the code is then needed.
Finally, note that the Ans variable is now declared as vbMsgBoxResult. Actually, that doesn't change the effect of the code in run-time, during debugging however it gives a little more insight than plain numeric values.
Hope this has clarified a number of things for you.

Sub Willow1985_r2()

    Const cMsg      As String = "The Report for @CellRef@ will be run," & vbCrLf & vbCrLf & " Do you wish to continue?"
    Const cWbPath   As String = "X:\QUALITY\Quality Assurance\"
    Const cWbFile   As String = "@CellRef@ Reports for Audits.xlsx"

    Dim sCellText As String, sWbFile As String, Ans As VbMsgBoxResult
    Dim Msg As String, lr As Long, sh As Worksheet, lo As ListObject

    sCellText = Sheets("Criteria").Range("A3").Value    ' << change as required
    sWbFile = Replace(cWbFile, "@CellRef@", sCellText)
    Msg = Replace(cMsg, "@CellRef@", sCellText)

    Application.ScreenUpdating = False
    If Application.CountIf(Sheets("Criteria").Range("A3:D3"), "") > 0 Then
        MsgBox "Please Complete all Fields"
        Exit Sub
    End If

    Ans = MsgBox(Msg, vbYesNo)

    Select Case Ans

        Case vbYes
            Workbooks.Open cWbPath & sWbFile
'MsgBox "The active window equals " & ActiveWindow.Caption  ' << uncomment this line to confirm
            Windows(sWbFile).Activate                       ' << superfluous but does not harm
            Application.Run sCellText

        Case vbNo
            GoTo Quit:      ' << also superfluous but does not harm
    End Select
Quit:
End Sub

 

[willow1985]

The Application.Run method is a kind of equivalent to the Call statement, so it does exactly what you requested.
As you said in your first post ...

the value in that particular cell is assigned to the sCellText variable, which is used to
- compose the required screen message;
- compose the required path\file specification;
- call a macro with a name equal to the containings of that variable.
Needless to say the macro has to be available and its name must meet the requirements of Excel / VBA (among others no use of numeric character in front, no use of space or following characters ().!{}[]\|=+$#@&^~, name can't exceed 255 characters in length).


In the context of your provided code this line is superfluous due to the first previous line, because whenever a workbook is opened, the window of that particular workbook becomes activated automatically (it gets focus), so there is no use in activating that same window (again) since it is already active. If you want to maintain that code line, there is nothing wrong with that, although a minor modification in the code is then needed.
Finally, note that the Ans variable is now declared as vbMsgBoxResult. Actually, that doesn't change the effect of the code in run-time, during debugging however it gives a little more insight than plain numeric values.
Hope this has clarified a number of things for you.

Sub Willow1985_r2()

    Const cMsg      As String = "The Report for @CellRef@ will be run," & vbCrLf & vbCrLf & " Do you wish to continue?"
    Const cWbPath   As String = "X:\QUALITY\Quality Assurance\"
    Const cWbFile   As String = "@CellRef@ Reports for Audits.xlsx"

    Dim sCellText As String, sWbFile As String, Ans As VbMsgBoxResult
    Dim Msg As String, lr As Long, sh As Worksheet, lo As ListObject

    sCellText = Sheets("Criteria").Range("A3").Value    ' << change as required
    sWbFile = Replace(cWbFile, "@CellRef@", sCellText)
    Msg = Replace(cMsg, "@CellRef@", sCellText)

    Application.ScreenUpdating = False
    If Application.CountIf(Sheets("Criteria").Range("A3:D3"), "") > 0 Then
        MsgBox "Please Complete all Fields"
        Exit Sub
    End If

    Ans = MsgBox(Msg, vbYesNo)

    Select Case Ans

        Case vbYes
            Workbooks.Open cWbPath & sWbFile
'MsgBox "The active window equals " & ActiveWindow.Caption  ' << uncomment this line to confirm
            Windows(sWbFile).Activate                       ' << superfluous but does not harm
            Application.Run sCellText

        Case vbNo
            GoTo Quit:      ' << also superfluous but does not harm
    End Select
Quit:
End Sub

This works perfectly, I made some adjustments and added another cell reference but it works great!

Thank you very much for clarifying everything

 

[willow1985]

The Application.Run method is a kind of equivalent to the Call statement, so it does exactly what you requested.
As you said in your first post ...

the value in that particular cell is assigned to the sCellText variable, which is used to
- compose the required screen message;
- compose the required path\file specification;
- call a macro with a name equal to the containings of that variable.
Needless to say the macro has to be available and its name must meet the requirements of Excel / VBA (among others no use of numeric character in front, no use of space or following characters ().!{}[]\|=+$#@&^~, name can't exceed 255 characters in length).


In the context of your provided code this line is superfluous due to the first previous line, because whenever a workbook is opened, the window of that particular workbook becomes activated automatically (it gets focus), so there is no use in activating that same window (again) since it is already active. If you want to maintain that code line, there is nothing wrong with that, although a minor modification in the code is then needed.
Finally, note that the Ans variable is now declared as vbMsgBoxResult. Actually, that doesn't change the effect of the code in run-time, during debugging however it gives a little more insight than plain numeric values.
Hope this has clarified a number of things for you.

Sub Willow1985_r2()

    Const cMsg      As String = "The Report for @CellRef@ will be run," & vbCrLf & vbCrLf & " Do you wish to continue?"
    Const cWbPath   As String = "X:\QUALITY\Quality Assurance\"
    Const cWbFile   As String = "@CellRef@ Reports for Audits.xlsx"

    Dim sCellText As String, sWbFile As String, Ans As VbMsgBoxResult
    Dim Msg As String, lr As Long, sh As Worksheet, lo As ListObject

    sCellText = Sheets("Criteria").Range("A3").Value    ' << change as required
    sWbFile = Replace(cWbFile, "@CellRef@", sCellText)
    Msg = Replace(cMsg, "@CellRef@", sCellText)

    Application.ScreenUpdating = False
    If Application.CountIf(Sheets("Criteria").Range("A3:D3"), "") > 0 Then
        MsgBox "Please Complete all Fields"
        Exit Sub
    End If

    Ans = MsgBox(Msg, vbYesNo)

    Select Case Ans

        Case vbYes
            Workbooks.Open cWbPath & sWbFile
'MsgBox "The active window equals " & ActiveWindow.Caption  ' << uncomment this line to confirm
            Windows(sWbFile).Activate                       ' << superfluous but does not harm
            Application.Run sCellText

        Case vbNo
            GoTo Quit:      ' << also superfluous but does not harm
    End Select
Quit:
End Sub

quick question, how do I close the workbook with out saving?

Workbooks.close cWbPath & sWbFile False

Is not the correct way to organize this....

Sorry, still learning.

Thank you

 

[willow1985]

Nevermind, got it:

Workbooks(sWbFile).Close False

 

[GWteB]

This works perfectly, I made some adjustments and added another cell reference but it works great!

Thank you very much for clarifying everything

You are welcome and thanks for letting me know.