Running a macro in a predetermine range if not them msgBox
 

I have CommandButton that when you click on it, it triggers a Form with a
List and when you click on an value then it pastes the value from the list
into the selected cell (B5). Also it will check for the value in cell E1 in
the sheet named "formula" and then it will Offset 3 cell(E5). Here is the
problem, I want for the macro only to execute in the range on B5:B159 and if
not in range B5:B159 then a message would say something like "Select a Cell
in Column B".
I have columns D and F with formulas and if anyone chooses an unlocked
(column C, D or E) the 3dr cell changes even if it is locked. Here is a piece
of code of what I have at the moment. I really appreciate your response,
Thanks


Private Sub CommandButton1_Click()
If lstSelection.ListIndex = -1 Then
MsgBox "No item selected", vbExclamation
Exit Sub
End If
Range("SelectionLink") = lstSelection.ListIndex + 1
Selection.Cells(1) = Worksheets("Formulas").Range("D1")
Selection.Cells(1).Offset(0, 3) = Worksheets("Formulas").Range("E1")
Unload Me
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub lstSelection_Click()

End Sub

Private Sub UserForm_Click()

End Sub

KBREnner

Running a macro in a predetermine range if not them msgBox
 

Hi KBREnner,

you should add test if user is in "allowed" area into your code.

Something like:

on error resume next
If Not Intersect(activecell, Range("b5..b159")) Is Nothing Then
on error goto 0
'here add what you want to do if "allowed" area
end if
on error goto 0

regards,
Ivan

Running a macro in a predetermine range if not them msgBox
 

Ivan, Thank you so much!
There is only one other thing I would like for the macro to do and that is
to give a MsgBox when On Error GoTo 0. I can live with out that nicesity,
I'll be looking for possible example in this forum and in Google to see if I
can figure it out!

Thanks, that really helps.
K Brenner


"Ivan Raiminius" wrote:

Hi KBREnner,

you should add test if user is in "allowed" area into your code.

Something like:

on error resume next
If Not Intersect(activecell, Range("b5..b159")) Is Nothing Then
on error goto 0
'here add what you want to do if "allowed" area
end if
on error goto 0

regards,
Ivan