Tom, Thank you so much! I was doing it th wrong way and I could not figure it
out.
KBrenner

"Tom Ogilvy" wrote:

Private Sub CommandButton1_Click()
'if the listindex of listbox equals -1 ... nothing selected
If lstSelection.ListIndex = -1 Then
MsgBox "No item selected", vbExclamation
Exit Sub
End If
If Not Intersect(ActiveCell, Range("b5..b159")) Is Nothing Then
On Error GoTo 0
Range("SelectionLink") = lstSelection.ListIndex + 1
Selection.Cells(1) = Worksheets("Formulas").Range("D1")
Selection.Cells(1).Offset(0, 3) = Worksheets("Formulas").Range("E1")
Else
msgbox "ActiveCell must be in B5:B159"
End if
Unload Me
End Sub

--
Regards,
Tom Ogilvy


"KBrenner" wrote in message
...
First let me say, Thanks to all the People that make this forum great!
All of my limited knowledge comes from browsing this forum and the
referred
websites for ideas, and that is how I been able to make most of my simple
macros.

I have a ComboBox with a list, when I click on an item in the list, the
value unloads in the selected cell. I also created a UserForm with a
ListBox
that is linked to the same List as the Combobox. The reason I wanted a
UserForm is because I can use the keyboard to navigate through the list
and
the OK and Cancel buttons with out using the mouse. Also I wanted for the
list to only unload in range B5:B159" and nowhere else.
What I would like to do is to get a Msgbox, letting the user know that
nothing was done because the macro will only work on a Range B5:B159. I
been
trying to use the If/Then commands but I'm not getting it right. Also the
Ok
button does not work if not in Range B5:B159.
Thanks in Advance

This is what I have so far:

Private Sub CommandButton1_Click()
'if the listindex of listbox equals -1 ... nothing selected
If lstSelection.ListIndex = -1 Then
MsgBox "No item selected", vbExclamation
Exit Sub
End If
If Not Intersect(ActiveCell, Range("b5..b159")) Is Nothing Then
On Error GoTo 0
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 If
On Error GoTo 0
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub lstSelection_Click()
End Sub
Private Sub UserForm_Click()
End Sub

KBrenner