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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Try removing the dollar signs from the code. You don't need them anyway.

Also ensure that there is a sheet named "CaseSelect". To prevent a crash due to a sheet name that doesn't exist, use the sheet number rather than the sheet name. In the VBA editor on the left navigation, there is your sheets. They have your sheet names and sheet numbers. Change this:
If Sheets("CaseSelect").Range("$A$1").Value = "GP1BR1S" Then
To this:
If Sheets(Sheet1.Name).Range("$A$1").Value = "GP1BR1S" Then
 
Upvote 0
Make sure you disable events before running your code to stop the changed event being triggered again.

Code: 
Application.EnableEvents = False

'Your Code Here

Application.EnableEvents = True
 
Upvote 0
Hi CB1994, welcome to the boards.

I am pleased to hear that the suggestions above have resolved your issue. On a slightly different note I think your first macro (the Worksheet_Change one) could be simplified immensely, reducing all those If / ElseIf's down to a less resource hungry version as follows:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$A$1" Then
    Select Case Cell.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 If
  
End Sub
 
Last edited:
Upvote 0
Fishboy said:
Hi CB1994, welcome to the boards.

I am pleased to hear that the suggestions above have resolved your issue. On a slightly different note I think your first macro (the Worksheet_Change one) could be simplified immensely, reducing all those If / ElseIf's down to a less resource hungry version as follows:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$A$1" Then
    Select Case Cell.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 If
  
End Sub
Click to expand...



Hi Fishboy,

Thank you for your response. I'd definitely like a way to simplify the code. I've copied your code in, however when I run it nothing happens. I believe it may be because of the target address, as the Cell 'A1' is on a different worksheet?

I've tried replacing the second line with the following, however I haven't had any luck in getting it to work. Do you have any suggestions?

Code: 
If Target.Address = Sheets("CaseSelect").Range("A1") Then


Thanks Again.
 
Upvote 0
CB1994 said:
Hi Fishboy,

Thank you for your response. I'd definitely like a way to simplify the code. I've copied your code in, however when I run it nothing happens. I believe it may be because of the target address, as the Cell 'A1' is on a different worksheet?

I've tried replacing the second line with the following, however I haven't had any luck in getting it to work. Do you have any suggestions?

Code: 
If Target.Address = Sheets("CaseSelect").Range("A1") Then


Thanks Again.
Click to expand...
Hi again CB1994,

I am confused as to why you wouldn't have your Worksheet_Change event on your CaseSelect sheet itself. Having it on a different sheet seems counter-intuitive and prone to raise unnecessary complications that would be easily resolved by having the code in the right place.

That said, are the changes to cell A1 on the CaseSelect sheet being made manually or as the result of a formula?
 
Upvote 0
Fishboy said:
Hi again CB1994,

I am confused as to why you wouldn't have your Worksheet_Change event on your CaseSelect sheet itself. Having it on a different sheet seems counter-intuitive and prone to raise unnecessary complications that would be easily resolved by having the code in the right place.

That said, are the changes to cell A1 on the CaseSelect sheet being made manually or as the result of a formula?
Click to expand...


The changes being made result from a formula. l've tried your code with the Worksheet_Change Event on the CaseSelect sheet. (Sorry, but I'm new to using VBA, my understanding is that to do this I merely need to copy the code provided into the 'Sheet25 (CaseSelect)' Microsoft Excel Objects. Is this correct?) However now when I change any of the variables that result in a change of the value of 'AI' nothing happens.

Apologies if I'm not being very clear, but I am new to using VBA and am not familiar with all of the terms.


Thanks.
 
Upvote 0
CB1994 said:
The changes being made result from a formula. l've tried your code with the Worksheet_Change Event on the CaseSelect sheet. (Sorry, but I'm new to using VBA, my understanding is that to do this I merely need to copy the code provided into the 'Sheet25 (CaseSelect)' Microsoft Excel Objects. Is this correct?) However now when I change any of the variables that result in a change of the value of 'AI' nothing happens.

Apologies if I'm not being very clear, but I am new to using VBA and am not familiar with all of the terms.


Thanks.
Click to expand...
No worries, everyone starts somewhere and won't learn unless they ask questions ;)

Firstly it sounds like you have done it correctly. Just to be sure this would have been the process:

- Right click on the CaseSelect tab name at the bottom of the Excel window and select View Code
- In the new window that opens, copy and paste in my code

Secondly, I have just noticed that I made a typo in my code which would have stopped it from working as intended. You would need to change this line of code:

Rich (BB code): 
    Select Case Cell.Value

To this:

Rich (BB code): 
    Select Case Target.Value

Finally just to clarify, in your last post you said cell AI, but I guess that was just a mistake and should have read A1 right?
 
Upvote 0
Fishboy said:
No worries, everyone starts somewhere and won't learn unless they ask questions ;)

Firstly it sounds like you have done it correctly. Just to be sure this would have been the process:

- Right click on the CaseSelect tab name at the bottom of the Excel window and select View Code
- In the new window that opens, copy and paste in my code

Secondly, I have just noticed that I made a typo in my code which would have stopped it from working as intended. You would need to change this line of code:

Rich (BB code): 
    Select Case Cell.Value

To this:

Rich (BB code): 
    Select Case Target.Value

Finally just to clarify, in your last post you said cell AI, but I guess that was just a mistake and should have read A1 right?
Click to expand...


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.
 
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
663
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,220,965
Messages
6,157,119
Members
451,398
Latest member
rjsteward

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