Confirm Selection Before Running Macro

Hi,
I have an excel form where users can input modifications by selecting a range and then pressing a command button to input a value in the selection.

My goal is: to restrict the location where a user can select a range of cell and then execute the macro . The cells where the user can use the macro are merged and in my code, I used the address as show in the address bar in excel for their location. If the user select a cell that is not permitted, a message box is displays and exits the code.

If (Selection.Address <> Range("AE7").Address) _
Or (Selection.Address <> Range("BM7").Address) Then
Msgbox ("Wrong location") Else

For some reason it doesn't work since the message box appears even if I am within the permitted locations.

Note: AE7 is the first cell of a merge, same as with BM7

Thank you so much

Re: Cell Restriction

Hi, thanks for the reply

I tried changing my "or" to "and" but it still doesn't work.

If Selection.Address <> Range("AE7").Address _
And Selection.Address <> Range("BM7").Address Then
Msgbox ("Wrong location") Else

Re: Cell Restriction

Hi Reafidy,
Your code works well but it is a bit complicated for me to understand. In your code the restricted cell are where nothing can be changed. I want the inverse, I want the specific cells (ae7 & bm7) to be the only locations where modifications CAN be made.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi, I was able to switch the restrictions but I don't know how to get rid of the inputbox. I'm thinking that it is around there that I should delete something.

On Error Resume Next
    Set rSelect = Application.InputBox(Prompt:="Please Select A Range...", Type:=8)
    If Err.Number <> 0 Then
         MsgBox "You Have Not Selected Anything!", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0

But when I try to delete this portion it gives me a runtime error 5, invalid procedure call or argument.

Thanks I really appreciate all the help.
Note: This code is part of the code posted by reafidy

Re: Restrict Users Cell Selection

Thank you very much for your help IT WORKS. I am sorry that I PM you. I thought that people didn't really check the forum so often. I'm kind of new and I didn't mean to be pushy.

Thank you for your help it is appreciated

Re: Restrict Users Cell Selection

Hi I found another issue in the code. The user can only run the macro in the cells that I specified but the user can select more then one column even if the other cells selected are not part of the "valid cells". This causes the macro to run in invalid cells. I want the user to be able to select only one column at a time. Here is my full code:

Dim rSelect As Range
    Dim aRanges As Variant
    Dim iLoop As Integer
    Set rSelect = Selection


    aRanges = Array("AE7", "BM7", "bx6", "bz4", "bm21", "dd14", "db31", "db35", "dd39", "Am64", "ch64", "dd72")
    For iLoop = 1 To UBound(aRanges)
        If Not Intersect(rSelect, Range(aRanges(iLoop))) Is Nothing Then GoTo AddRevision  'I'm trying to force the user to only select one column
    
    Next iLoop
    MsgBox "You Have Selected An Invalid Range"
    Exit Sub
AddRevision:
   With Selection
       .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
   End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
       .ColorIndex = 3
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone


Selection.Value = Range("Cw6")
    
    
    
End Sub

On another worksheet instead of having specific cells where users can run the macro, I have a range (R9:R52). I tried to substitue the Array with a range but it is not working.

aRanges = Array("AE7", "BM7", "bx6", "bz4", "bm21", "dd14", "db31", "db35", "dd39", "Am64", "ch64", "dd72")

with

aRanges = Range("R9:R52")


Here is the full code
[code]
Dim rSelect As Range
    Dim aRanges As Variant
    Dim iLoop As Integer
    Set rSelect = Selection


    aRanges = Array("R9", "r10", "r11", "r12", "r13", "r14", "r15", "r16", "r17", "r18", "r19", "r20", _
    "r21", "r22", "r23", "r24", "r25")
    For iLoop = 1 To UBound(aRanges)
        If Not Intersect(rSelect, Range(aRanges(iLoop))) Is Nothing Then GoTo AddRevision_p2
    
    Next iLoop
    MsgBox "You Have Selected An Invalid Range"
    Exit Sub


AddRevision_p2: 
...

I have the same issue on this worksheet, I want the user to be able to only select one column at a time.

Thanks
Dom01

Re: Restrict Users Cell Selection

Hi,
The sheet protection will not work because the other cells must be edited to enter text and such.

Re: Confirm Selection Before Running Macro

Hi,
Thanks for the columns.count tip. I have a small problem. The cells where the user is allowed to run the macro is a mix of merged cells and single cells. When the selection is a merged cell the macro run but when the selection is a single cell it is not recognized as a valid selection. I think the problem is here:

Dim aRanges As Variant 
aRanges =  Array("AE7", "BM7", "bx6", "bz4", "bm21", "dd14", "db31", "db35", "dd39", "Am64", "ch64", "dd72")

Where bz4,db31 & db35 are single cells and the rest are merged cells.
Thanks

Re: Confirm Selection Before Running Macro

Hi,
Since it is a standard company wide form, I can't really play with the formatting of the cell this way. By the way what is the really problem when dealing with merged cells in vba.

Thanks

Re: Confirm Selection Before Running Macro

Hi,
I can't really use the center across because the merged cells are vertical. And there is no vertical center across selection option. I don't understand why the code is not applicable when the cells are not merged i.e. when a single cell is selected.

Is it because of the array? I am completely clueless

Dim aRanges As Variant 
aRanges =  Array("AE7", "BM7", "bx6", "bz4", "bm21", "dd14", "db31", "db35", "dd39", "Am64", "ch64", "dd72")