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

What is the event you are using to show the UserForm

--
davesexce

-----------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...fo&userid=3170
View this thread: http://www.excelforum.com/showthread.php?threadid=53515

DaveExel,
When I click on CommandButton the form appears, then I select from the list
with the key board. and then click Ok or Alt+O. I really like this better
becouse I can scroll very fast though a list of 200 to 500 values by
MatchEntryFirstLetter.

Private Sub CommandButton_Click()
UserForm1.Show
End Sub

Thanks
KBrenner


"davesexcel" wrote:


What is the event you are using to show the UserForm ?


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=535158

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

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