Check Active Cell Before Running Code

    I have a macro that is intdended to run after the user has selected a cell in column A:A (any position except A1)
    Once the user has selected their desired cell, they press a button and the macro runs.
    Occasionally a user will press the button without selecting a cell in the proper column.
    I would like a message box to pop up to tell the user "Please select an account in Column A"
    and bump the cursor (active cell) to A2 so at least it's in the right column to help get them started. Appreciate any help!

    Re: Check If Active Cell Is In Specified Column


    Jaffey,


    Try this:




    Have a great day,
    Stan

    Re: Check If Active Cell Is In Specified Column


    Try this:

    Code
    Sub A_TEST()
   If ActiveCell.Column <> 1 Or ActiveCell.Row = 1 Then
      MsgBox "Please select an cell in Column A, other than A1."
      Range("A2").Select
      Exit Sub
   End If


   'your code here
End Sub

    Re: Check If Active Cell Is In Specified Column


    The message box pops up properly but it doesn't allow the user to select a new cell. It's just moving the active cell to A2 as per the code and then executing the rest of the script. I need it to give the user a few seconds to select a cell. I tried inserting

    Code
    Application.Wait Now + TimeSerial(0, 0, 3)

    after your code but that just gave me 3 seconds of hourglass before proceeding with the rest of the script and I couldn't select a new cell.

    Re: Check If Active Cell Is In Specified Column


    Quote from Jaffey

    It's just moving the active cell to A2 as per the code and then executing the rest of the script.


    Are you sure about this? Both of the subs above should exit entirely if condition isn't met.

    Re: Check If Active Cell Is In Specified Column


    I think they are exiting but as I said the rest of the code then fires before the user has had a chance to select their desired cell in column 1. While the message box is displayed the can't select anything and as soon as they click OK on the message box the rest of the code executes. The timer I tried adding doesn't allow the user to change cell selections while it's counting down. I need something to return the focus to the user so they can pick a cell before it proceeds.

    Re: Check If Active Cell Is In Specified Column


    Quote

    Are you sure about this? Both of the subs above should exit entirely if condition isn't met.


    Im guessing the op has placed the code into the worksheet change event which means you will be getting a unwanted cycle. You will need to disable events:


    Code
    If ActiveCell.Column <> 1 Or ActiveCell.Row = 1 Then 
        MsgBox "Please select an cell in Column A, other than A1."
        application.enableevents = false
        Range("A2").Select 
        application.enableevents = true
        Exit Sub 
    End If


    I would just use:


    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A2:A" & Rows.Count)) Is Nothing Then Exit Sub
    'Your Code Here
End Sub


    If it is really a command button as you first said then the original code should work.

    Re: Check If Active Cell Is In Specified Column


    The section of code that reads "your code here" is where your code is supposed to go. Do you have your code somewhere else.

    Re: Check If Active Cell Is In Specified Column


    Here is the code (yes I know, the dreaded sendkeys....I'm totally open to alternatives)


    Re: Check If Active Cell Is In Specified Column


    Here is the easiest fix:

    Code
    Sub AUDIT_FILE()
    If ActiveCell.Column <> 1 Or ActiveCell.Row = 1 Then
         MsgBox "Please select an account in Column A and then rerun the macro"
         Range("A2").Select
         Exit Sub
    End If
    If IsEmpty(Sheets("Credentiales").Range("A1")) Then ufCredentiales.Show
    Open_Audit_File
End Sub

    Re: Check If Active Cell Is In Specified Column


    In that case I need it to skip the last line "open-audit_file" or it will start the next macro so I changed it to

    Code
    Sub AUDIT_FILE()
    If ActiveCell.Column <> 1 Or ActiveCell.Row = 1 Then
         MsgBox "Select an account in Column A and try again"
         Range("A2").Select
         GoTo 1
    End If
    If IsEmpty(Sheets("Credentiales").Range("A1")) Then ufCredentiales.Show
    Open_Audit_File
1
End Sub


    and now it exits completely if they are in the wrong column. This is definitely better than pausing anyway. Thanks Turtle44! Thanks Stan!

    Re: Check If Active Cell Is In Specified Column


    You're welcome, but why are you over-complicating the code. The "Exit Sub" in my code does the same thing as the "GoTo 1" in your code.
    Also, please edit your last post, the closing code tag should end with "]", not "}"

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login