Problem with macro in Excel VBA working across sheets

Excel Discussion in 'Microsoft Office' started by Bitsbb01, Apr 9, 2012.

  1. Bitsbb01


    Apr 9, 2012
    Likes Received:
    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..

    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..
    Bitsbb01, Apr 9, 2012
    1. Advertisements

  2. Bitsbb01


    Apr 19, 2012
    Likes Received:
    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
    nehanaik25, Apr 20, 2012
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.