Hi there,
I'm completely new to Excel VBA and I'm struggling to get a macro to work.
I realise that for a newbie I might be trying to bite off more than I can chew *facepalm*
Context:
I have several 'product sheets' (14 in total) with each sheet containing information on the available versions for said product. The idea is that product managers will update these sheets as new product versions become available.
I then have another sheet (called 'customer products') which has all the product versions (from all 14 sheets) in the columns and a list of clients in the rows. This sheet provides a view of which customers have which products. Product managers will need to add any new versions to this sheet as well.
In an attempt to ensure there are no products missing from this sheet I want to include macros to cross-check the products between the 'customer products' sheet and the individual product sheets.
On each of the product sheets, I have the below macro which works fine.
There's a formula on each product sheet which flags when products are missing from the 'customer products' sheet. If errors are found, a warning message pops up on the relevant product sheet. l have this macro on all 14 product sheets.
Private Sub Worksheet_Calculate()
Static MyOldVal
If Range("$C$42").Value > 0 Then
Call Message_ProdWarning
End If
End Sub
I however want to stop the abovementioned macro from running when I'm on the 'customer products' sheet. I've tried the following but I just can't get it to work:
Private Sub Worksheet_Calculate_CMS()
Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets
For Each ws In wb.Worksheets
If ws.Name = "Customer products" Then
Exit Sub
Else
Static MyOldVal
If Range("$C$42").Value > 0 Then
Call Message_ProdWarning
End If
End If
End Sub
The other problem I have is that I want to run a different macro on the 'customer products' sheet. Again cross-checking that all products on this sheet have been added to the relevant product sheet. I've added formulas to flag if a product on the 'customer products' sheet is a duplicate or missing from the relevant product sheet. So here I need an error message based on 2 values changing - i.e. the error message should show if one or both of the relevant cells change.
I've added the following macro for this:
Sub Calculate_Customer()
Static MyOldVal
If Range("$E$217").Value > 0 Or Range("$E$219").Value > 0 Then
Call Message_CustWarning
End If
End Sub
The macro works when I "run" it in Visual Basic, but when I'm on the sheet, it doesn't work.
It should show me the error message below, but instead, it shows me the error message that should only show on the product sheets (the one I shared above).
I'm not sure if the issue is that when I change something on the 'customer products' sheets, it triggers the specified cell on the product sheet to change, which then calls the first error message (i.e. not the message below).
Am I overengineering this? I'd appreciate any feedback.
Thanks!
I'm completely new to Excel VBA and I'm struggling to get a macro to work.
I realise that for a newbie I might be trying to bite off more than I can chew *facepalm*
Context:
I have several 'product sheets' (14 in total) with each sheet containing information on the available versions for said product. The idea is that product managers will update these sheets as new product versions become available.
I then have another sheet (called 'customer products') which has all the product versions (from all 14 sheets) in the columns and a list of clients in the rows. This sheet provides a view of which customers have which products. Product managers will need to add any new versions to this sheet as well.
In an attempt to ensure there are no products missing from this sheet I want to include macros to cross-check the products between the 'customer products' sheet and the individual product sheets.
On each of the product sheets, I have the below macro which works fine.
There's a formula on each product sheet which flags when products are missing from the 'customer products' sheet. If errors are found, a warning message pops up on the relevant product sheet. l have this macro on all 14 product sheets.
Private Sub Worksheet_Calculate()
Static MyOldVal
If Range("$C$42").Value > 0 Then
Call Message_ProdWarning
End If
End Sub
I however want to stop the abovementioned macro from running when I'm on the 'customer products' sheet. I've tried the following but I just can't get it to work:
Private Sub Worksheet_Calculate_CMS()
Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets
For Each ws In wb.Worksheets
If ws.Name = "Customer products" Then
Exit Sub
Else
Static MyOldVal
If Range("$C$42").Value > 0 Then
Call Message_ProdWarning
End If
End If
End Sub
The other problem I have is that I want to run a different macro on the 'customer products' sheet. Again cross-checking that all products on this sheet have been added to the relevant product sheet. I've added formulas to flag if a product on the 'customer products' sheet is a duplicate or missing from the relevant product sheet. So here I need an error message based on 2 values changing - i.e. the error message should show if one or both of the relevant cells change.
I've added the following macro for this:
Sub Calculate_Customer()
Static MyOldVal
If Range("$E$217").Value > 0 Or Range("$E$219").Value > 0 Then
Call Message_CustWarning
End If
End Sub
The macro works when I "run" it in Visual Basic, but when I'm on the sheet, it doesn't work.
It should show me the error message below, but instead, it shows me the error message that should only show on the product sheets (the one I shared above).
I'm not sure if the issue is that when I change something on the 'customer products' sheets, it triggers the specified cell on the product sheet to change, which then calls the first error message (i.e. not the message below).
Am I overengineering this? I'd appreciate any feedback.
Thanks!
