Run a specific Macro in response a specific Cell value change.

C

CB1994

New Member
Joined
Jun 29, 2016
Messages
14
Hi All,

I am having a problem with getting a specific Macro to run when the text in a cell is changed to a corresponding value. What I would like is for a Macro to be ran on one worksheet (Report) when the value of a cell (A1) on another worksheet (CaseSelect) is changed.

The value of the cell is determined by a number of inputs from the first worksheet. As these inputs are changed I would like for the appropriate Macro to be called automatically. In total there are 15 possible values, for Cell 'A1' (ie. 'GP1BR1S'), based on the various inputs, and 15 corresponding Macros (ie. 'GP1BR1S').

below is the code I am trying to get to work.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Sheets("CaseSelect").Range("$A$1").Value = "GP1BR1S" Then
        Call GP1BR1S


    ElseIf Sheets("CaseSelect").Range("A1") = "GP2BR1S" Then
        Call GP2BR1S


    ElseIf Sheets("CaseSelect").Range("A1") = "GP3BR1S" Then
        Call GP3BR1S


    ElseIf Sheets("CaseSelect").Range("A1") = "GP1BR1SA" Then
        Call GP1BR1S


    ElseIf Sheets("CaseSelect").Range("A1") = "GP2BR1SA" Then
        Call GP2BR1S


    ElseIf Sheets("CaseSelect").Range("A1") = "GP3BR1SA" Then
        Call GP3BR1S


    ElseIf Sheets("CaseSelect").Range("A1") = "GP1BR2S2B" Then
        Call GP1BR2S2B


    ElseIf Sheets("CaseSelect").Range("A1") = "GP1BR2S1I" Then
        Call GP1BR2S1I


    ElseIf Sheets("CaseSelect").Range("A1") = "GP1BR2S2I" Then
        Call GP1BR2S2I


    ElseIf Sheets("CaseSelect").Range("A1") = "GP2BR2S2B" Then
        Call GP2BR2S2B


    ElseIf Sheets("CaseSelect").Range("A1") = "GP2BR2S1I" Then
        Call GP2BR2S1I


    ElseIf Sheets("CaseSelect").Range("A1") = "GP2BR2S2I" Then
        Call GP2BR2S2I


    ElseIf Sheets("CaseSelect").Range("A1") = "GP3BR2S2B" Then
        Call GP3BR2S2B


    ElseIf Sheets("CaseSelect").Range("A1") = "GP3BR2S1I" Then
        Call GP3BR2S1I


    ElseIf Sheets("CaseSelect").Range("A1") = "GP3BR2S2I" Then
        Call GP3BR2S2I


    Else
      ' Do nothing


    End If
  
End Sub

When I change one of the inputs I get "Run-Time error '-2147417848 (80010108)': Method 'Range' of object '_Worksheet' failed"

When I debug this it highlights my second line of code, "If Sheets("CaseSelect").Range("$A$1").Value = "GP1BR1S" Then" as being the problem.


An interesting point to note is that it seems as though the Macro GP1BR1S is being called as some of the formatting is being changed, something that the Macro is supposed to do. However none of the other functions of the Macro are being ran.

All of the Macros do essentially the same thing, which is to clear the existing contents of certain cells, and then to replace the contents of those cells with the contents of another worksheet.

Below is an example of one of the Macros (GP1BR1S);
Code: 
Sub GP1BR1S()
'
' GP1BR1S Macro
'


'
Application.ScreenUpdating = False
Call UnprotectAll


    ActiveSheet.DrawingObjects.Select
    Selection.Delete
    
    Sheets("Report").Range("W20:AL46").Select
    Selection.Clear
    
    Sheets("GP1BR1S").Range("W20:AL46").Copy Destination:=Sheets("Report").Range("W20")
    
    Sheets("GP1BR1S").Range("W61:AL87").Copy Destination:=Sheets("Report").Range("W61")
    
    Sheets("GP1BR1S").Range("U105:AN142").Copy Destination:=Sheets("Report").Range("U105")
           
    
    Sheets("GP1BR1S").Range("$AJ$13:$AL$13").Copy Destination:=Sheets("Report").Range("$AJ$17:$AL$17")
    
    Sheets("GP1BR1S").Range("$AJ$14:$AL$14").Copy Destination:=Sheets("Report").Range("$AJ$18:$AL$18")
    
   ' Tab Plate End Distance
    Range("Z17:AB17").Select
    ActiveCell.FormulaR1C1 = "=Report!R[8]C[4]"
    Range("Z18:AB18").Select
  
  ' Gusset Plate/Tab Plate Width
    Range("AJ18:AL18").Select
    ActiveCell.FormulaR1C1 = "=Report!R[14]C"
    Range("AJ19").Select
  
  ' Gusset Plate Length Value
    Range("Z18:AB18").Select
    ActiveCell.FormulaR1C1 = "=R[-2]C+R[-1]C+R[-1]C[10]+5-R[2]C[-21]-5"
    Range("Z19").Select
    
    
    Worksheets("SHS 1 - 2").Shapes("Picture 2").Copy
    Worksheets("Report").Paste Range("G24")
    


    
    Worksheets("SHS 1 - 2").Shapes("Picture 8").Copy
    Worksheets("Report").Paste Range("G37")
    
    Sheets("SHS 1 - 2").Range("A105:T142").Copy Destination:=Sheets("Report").Range("A105")
    
Call ProtectAll
Application.ScreenUpdating = True
    
End Sub



Thanks in advanced for any help you can give me. If you need me to give you more information about the code or the worksheets then just let me know. Also I'm fairly new to using VBA and would appreciate it if you could explain any answers you can provide, just to help me learn and understand.


Kind regards,

Connor
 
Sort by date Sort by votes
CB1994 said:
Yes, I did mean A1 :p. I've copied the code in, but nothings happening still. Will it matter that the code is in the CaseSelector worksheet, when I want the Macros to run on the Report worksheet? I cant really see how this would be the case as the Macros each specifically reference the Report worksheet, but I don't really know what else to suggest.

Thanks.
Click to expand...
Hmm, curious...

You can download the very simplified test workbook I wrote my code in from HERE. You will notice I had to improvise a little as I have no idea what each of these other macros are supposed to be doing. Instead I created a dummy macro for each one which just displays a simple message box to confirm the correct macro is running when the value of A1 is changed.

If you can test with my workbook just to ensure that the corresponding macro is being called then that is at least a good start.
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Fishboy said:
Hmm, curious...

You can download the very simplified test workbook I wrote my code in from HERE. You will notice I had to improvise a little as I have no idea what each of these other macros are supposed to be doing. Instead I created a dummy macro for each one which just displays a simple message box to confirm the correct macro is running when the value of A1 is changed.

If you can test with my workbook just to ensure that the corresponding macro is being called then that is at least a good start.
Click to expand...


Hi Fishboy,

Apologies for taking a while to respond to you. I've looked through the workbook you provided for me, and thank you for taking the time to do so.

I think one of the problems may be that my case select, isn't a case select in the sense that its a drop down list like yours, but is instead just a formula that selects the case automatically base on different inputs. Perhaps another problem is that the case select target cell is on a different sheet than the input variables and report area (where the Macro needs to be ran.)

I've tried altering the workbook you provided to reflect this, hopefully it will do a better job off explaining the situation than I have been able to so far.

Modified Workbook.

Again thank you so much for the help you've given.
 
Upvote 0
CB1994 said:
Hi Fishboy,

Apologies for taking a while to respond to you. I've looked through the workbook you provided for me, and thank you for taking the time to do so.

I think one of the problems may be that my case select, isn't a case select in the sense that its a drop down list like yours, but is instead just a formula that selects the case automatically base on different inputs. Perhaps another problem is that the case select target cell is on a different sheet than the input variables and report area (where the Macro needs to be ran.)

I've tried altering the workbook you provided to reflect this, hopefully it will do a better job off explaining the situation than I have been able to so far.

Modified Workbook.

Again thank you so much for the help you've given.
Click to expand...
OK, so now I can see how it will be looking at your end I can see some complications / questions arise...

1. As cell A1 on the Case Select sheet is the result of a formula then we cannot use a Worksheet_Change event on this sheet as it cannot be triggered by formula results.

2. In these cases we would usually swap out to a Worksheet_Calculate event instead as this will trigger from formula results, however it would be triggered each and every time the sheet is recalculated (possibly on any change in the workbook) meaning the code would potentially keep firing off over and over again.

3. One possible solution would be to change the Worksheet_Change event to be on the Report sheet instead, so that once all 3 drop-downs have been altered the code then runs. Alternatively you would have to swap it out entirely for a standard macro which needs to be manually run on the press of a button.

If you can let me know which of the above you would prefer and we can see if we can get it amended to suit.
 
Upvote 0
Fishboy said:
OK, so now I can see how it will be looking at your end I can see some complications / questions arise...

1. As cell A1 on the Case Select sheet is the result of a formula then we cannot use a Worksheet_Change event on this sheet as it cannot be triggered by formula results.

2. In these cases we would usually swap out to a Worksheet_Calculate event instead as this will trigger from formula results, however it would be triggered each and every time the sheet is recalculated (possibly on any change in the workbook) meaning the code would potentially keep firing off over and over again.

3. One possible solution would be to change the Worksheet_Change event to be on the Report sheet instead, so that once all 3 drop-downs have been altered the code then runs. Alternatively you would have to swap it out entirely for a standard macro which needs to be manually run on the press of a button.

If you can let me know which of the above you would prefer and we can see if we can get it amended to suit.
Click to expand...


Ideally I would like it to try using the Worksheet_Calculate event. However I can already see a potential problem, if it triggers on any change in the workbook.

The macros I have written essentially just clear out all of the current data before copying in the corresponding data from another Calculations sheet. I can imagine it would therefore be difficult to change the different variables on the Report sheet, as the Macro would constantly be clearing the data.

If this is a problem though I can always look at rewriting the Macros to prevent them from clearing the input data, and instead just have them clear the previously calculated results.
 
Upvote 0
CB1994 said:
Ideally I would like it to try using the Worksheet_Calculate event. However I can already see a potential problem, if it triggers on any change in the workbook.

The macros I have written essentially just clear out all of the current data before copying in the corresponding data from another Calculations sheet. I can imagine it would therefore be difficult to change the different variables on the Report sheet, as the Macro would constantly be clearing the data.

If this is a problem though I can always look at rewriting the Macros to prevent them from clearing the input data, and instead just have them clear the previously calculated results.
Click to expand...
Will there be any other recalculations happening directly on the Case Select sheet? If not then I think we will be fine as fortunately the Worksheet_Calculate event applied to a specific worksheet module is only triggered when that specific sheet is recalculated.

You can test this out by right-clicking on the Case Select sheet tab and selecting View Code, then completely replacing my original Worksheet_Change event code with the following:

Code: 
Private Sub Worksheet_Calculate()


    Select Case Range("A1").Value
        Case "GP1BR1S"
            Call GP1BR1S
        Case "GP2BR1S"
            Call GP2BR1S
        Case "GP3BR1S"
            Call GP3BR1S
        Case "GP1BR1SA"
            Call GP1BR1S
        Case "GP2BR1SA"
            Call GP2BR1S
        Case "GP3BR1SA"
            Call GP3BR1S
        Case "GP1BR2S2B"
            Call GP1BR2S2B
        Case "GP1BR2S1I"
            Call GP1BR2S1I
        Case "GP1BR2S2I"
            Call GP1BR2S2I
        Case "GP2BR2S2B"
            Call GP2BR2S2B
        Case "GP2BR2S1I"
            Call GP2BR2S1I
        Case "GP2BR2S2I"
            Call GP2BR2S2I
        Case "GP3BR2S2B"
            Call GP3BR2S2B
        Case "GP3BR2S1I"
            Call GP3BR2S1I
        Case "GP3BR2S2I"
            Call GP3BR2S2I
        Case Else
            Exit Sub
    End Select
  
End Sub
 
Upvote 0
Fishboy said:
Will there be any other recalculations happening directly on the Case Select sheet? If not then I think we will be fine as fortunately the Worksheet_Calculate event applied to a specific worksheet module is only triggered when that specific sheet is recalculated.

You can test this out by right-clicking on the Case Select sheet tab and selecting View Code, then completely replacing my original Worksheet_Change event code with the following:

Code: 
Private Sub Worksheet_Calculate()


    Select Case Range("A1").Value
        Case "GP1BR1S"
            Call GP1BR1S
        Case "GP2BR1S"
            Call GP2BR1S
        Case "GP3BR1S"
            Call GP3BR1S
        Case "GP1BR1SA"
            Call GP1BR1S
        Case "GP2BR1SA"
            Call GP2BR1S
        Case "GP3BR1SA"
            Call GP3BR1S
        Case "GP1BR2S2B"
            Call GP1BR2S2B
        Case "GP1BR2S1I"
            Call GP1BR2S1I
        Case "GP1BR2S2I"
            Call GP1BR2S2I
        Case "GP2BR2S2B"
            Call GP2BR2S2B
        Case "GP2BR2S1I"
            Call GP2BR2S1I
        Case "GP2BR2S2I"
            Call GP2BR2S2I
        Case "GP3BR2S2B"
            Call GP3BR2S2B
        Case "GP3BR2S1I"
            Call GP3BR2S1I
        Case "GP3BR2S2I"
            Call GP3BR2S2I
        Case Else
            Exit Sub
    End Select
  
End Sub
Click to expand...


So, good and bad news. There is only the one calculation happening on the case select sheet, and all of the factors that effect the formula for the calculation are variables that I want to trigger a recalculation. Also in the good news is that something is happening now when the variables are changed using the above code you provided. Bad news is that it isn't working as intended, I believe this may be due to my Macros. Whats happening is that the formatting of the workbook is being changed completely and the Macros are getting to a certain line in their code before needing debugging. Interestingly it also makes it so that I cant exit the workbook without using Task Manager.

I'll play around with my Macros to see if they are the problem. Do you have any suggestions as to what may be happening?
 
Upvote 0
I've just realised what was causing this, I've had it happen before. I needed to enable Application Events.

Code: 
Private Sub Worksheet_Calculate()


Application.EnableEvents = False


    Select Case Range("A1").Value
        Case "GP1BR1S"
            Call GP1BR1S
        Case "GP2BR1S"
            Call GP2BR1S
        Case "GP3BR1S"
            Call GP3BR1S
        Case "GP1BR1SA"
            Call GP1BR1SA
        Case "GP2BR1SA"
            Call GP2BR1SA
        Case "GP3BR1SA"
            Call GP3BR1SA
        Case "GP1BR2S2B"
            Call GP1BR2S2B
        Case "GP1BR2S1I"
            Call GP1BR2S1I
        Case "GP1BR2S2I"
            Call GP1BR2S2I
        Case "GP2BR2S2B"
            Call GP2BR2S2B
        Case "GP2BR2S1I"
            Call GP2BR2S1I
        Case "GP2BR2S2I"
            Call GP2BR2S2I
        Case "GP3BR2S2B"
            Call GP3BR2S2B
        Case "GP3BR2S1I"
            Call GP3BR2S1I
        Case "GP3BR2S2I"
            Call GP3BR2S2I
        Case Else
            Exit Sub
    End Select


Application.EnableEvents = True


End Sub

It's working flawlessly now. Thank you so much for all of the help. It's really been appreciated!
 
Upvote 0
CB1994 said:
I've just realised what was causing this, I've had it happen before. I needed to enable Application Events.

Code: 
Private Sub Worksheet_Calculate()


Application.EnableEvents = False


    Select Case Range("A1").Value
        Case "GP1BR1S"
            Call GP1BR1S
        Case "GP2BR1S"
            Call GP2BR1S
        Case "GP3BR1S"
            Call GP3BR1S
        Case "GP1BR1SA"
            Call GP1BR1SA
        Case "GP2BR1SA"
            Call GP2BR1SA
        Case "GP3BR1SA"
            Call GP3BR1SA
        Case "GP1BR2S2B"
            Call GP1BR2S2B
        Case "GP1BR2S1I"
            Call GP1BR2S1I
        Case "GP1BR2S2I"
            Call GP1BR2S2I
        Case "GP2BR2S2B"
            Call GP2BR2S2B
        Case "GP2BR2S1I"
            Call GP2BR2S1I
        Case "GP2BR2S2I"
            Call GP2BR2S2I
        Case "GP3BR2S2B"
            Call GP3BR2S2B
        Case "GP3BR2S1I"
            Call GP3BR2S1I
        Case "GP3BR2S2I"
            Call GP3BR2S2I
        Case Else
            Exit Sub
    End Select


Application.EnableEvents = True


End Sub

It's working flawlessly now. Thank you so much for all of the help. It's really been appreciated!
Click to expand...
You're most welcome
 
Upvote 0
You must log in or register to reply here.

Similar threads

Woozypictures
  • Solved
VBA Code to copy specific rows from one sheet to another, based on cell value
Replies
6
Views
2K
Woozypictures
Woozypictures
G
  • Question
Excel VBA Macro steps through ok but when run it causes duplication
Replies
2
Views
4K
Gassy
G
A
  • Question
Automatically Merge data from a specific range from multiple sheets(avoid blank cells)
Replies
0
Views
669
andybc33
A
R
  • Question
Code works when stepping through, but not when running
Replies
5
Views
1K
Logit
Logit
T
  • Question
Excel VBA - Return to the sheet the Macro was called from
Replies
2
Views
1K
Trent452
T

Forum statistics

Threads
1,221,310
Messages
6,159,173
Members
451,543
Latest member
cesymcox

Share this page

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
Back
Top