Would like to getback a MsgBox on error

[KBrenner]

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

[davesexcel]

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

[KBrenner]

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
>
>

[Tom Ogilvy]

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
>

[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" <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
> >
>
>
>