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" <KBrenner@discussions.microsoft.com> wrote in message
> news:CAFA08E1-A2D1-49FF-B932-EBC9A15C3DFE@microsoft.com...
> > 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
> >
>
>
>