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

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
716
Office Version
  1. 365
Platform
  1. Windows
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! :)

VBA Code:
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Try this ...
VBA Code:
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
 
Solution

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
716
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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.


VBA Code:
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

Well-known Member
Joined
Jul 24, 2019
Messages
716
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.


VBA Code:
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 :giggle:
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
716
Office Version
  1. 365
Platform
  1. Windows
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.


VBA Code:
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?

VBA Code:
Workbooks.close cWbPath & sWbFile False

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

Sorry, still learning.

Thank you
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
716
Office Version
  1. 365
Platform
  1. Windows
Nevermind, got it:

Workbooks(sWbFile).Close False
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
This works perfectly, I made some adjustments and added another cell reference but it works great!

Thank you very much for clarifying everything :giggle:
You are welcome and thanks for letting me know.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,623
Messages
5,625,940
Members
416,143
Latest member
JoyceMB

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