Excel Problem with macro in Excel VBA working across sheets

B

Bitsbb01

Joined
Apr 9, 2012
Messages
2
Reaction score
0
How do i get the same macro format but for it to read from Sheet2 CX2:CX150 And only give the answer for the row that they are on in Sheet 1, eg, AT3 would run macro and show CX3

I have already worked out how to get the macro working, if someone clicks Yes in the listbox in Sheet 1, AT2.. it then runs the Worksheet_Calculate2 macro and checks to see if any of the cells are not correctly filled in (in this case, CX2 checks multiple cells) however when they goto AT3, and then click yes, then i'd need another macro for the excate same thing but for CX3..


Code: 
Sub Worksheet_Calculate2()
Application.ScreenUpdating = False
If Range("Sheet2!CX2").Value > 0 Then msgbox "Theres At least 1 Cell Not Filled in, Please check again and then continue"
End Sub

This is a stubbon problem and i've been trying to work on it for nearly a week now..
If i can just get some help on how to do this...

Thanks in advance..
 
Hi Bitsbb01,

If you are using worksheet level event then you can use offset, something like

Private Sub Worksheet_Change(ByVal Target As Range)
If VBA.InStr(1, Target.Address, "AT", vbTextCompare) Then
Worksheet_Calculate2 Target
End If
End Sub

Sub Worksheet_Calculate2(ByVal SelectedCell As Range)
Application.ScreenUpdating = False
If SelectedCell.Offset(0, 56).Value > 0 Then MsgBox "Theres At least 1 Cell Not Filled in, Please check again and then continue"
End Sub
 
Back
Top