Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro access to named range (Excel 97 SR2)

    I have a workbook with many sheets. The sheets have certain columns hidden. In cell N3 (which is hidden) is a formula that determines if a column has been tampered with. If N3 has a 1 there was no tampering, if it contains a 0 then tampering has occurred. I want to write a macro that checks cell N3 across the sheets and puts warning in a cell D15 of sheet Greetings if there is sign of tampering on any sheet. If no tampering is evident, it should blank cell D15 of sheet Greetings. Could I get some help on this one please?

    Thanks,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro access to named range (Excel 97 SR2)

    It's not clear to me what your question has to do with named ranges. You could use code like this:

    Sub Check4Tampering()
    Dim blnTampered As Boolean
    Dim wsh As Worksheet

    For Each wsh In ActiveWorkbook.Worksheets
    If wsh.Range("N3") = 0 Then
    blnTampered = True
    Exit For
    End If
    Next wsh

    If blnTampered Then
    Worksheets("Greeting").Range("D15") = "TAMPERED WITH!"
    Else
    Worksheets("Greeting").Range("D15").ClearContents
    End If
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro access to named range (Excel 97 SR2)

    Hans,
    Thanks for the code. The reason I mentioned named ranges was because I created a named range "Tampered" that gave me the min value of the range that spanned the sheets. But I had trouble writing VBA that would get the value. So I did a few searches and then wrote for help. I should NOT have even mentioned named range. I am sorry it was misleading. Thank you so much for the code. I will put it in after lunch.
    I really LOVE the way it is sooo easy to get help on this site.

    Thanks,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •