Help with Custom Dialog Box

This is my first attempt at a Dialog Box and I'm a bit stuck.

I've created a User Form which comprises a List box containing a list of
names and a Text Box to which the User adds text then if they press OK I want
the value selected in the List Box to be "looked up" against a list in cells
A4:A13 and the text from the text box to be appended alongside that name in
Col B.

Having designed the form I want it to run when a button is pressed on the
sheet but I'm not sure what instructions to add to the button macro to make
the dialog box appear and then do what I've described above.

I hope this is clear and I'd be grateful for any help. Thanks in
anticipation.

With Me.ListBox1
If .ListIndex <> -1 Then
Worksheets("Sheet1").Range("A4:A13")(.ListIndex, 1).Value = _
.Value & Me.TextBox1.Text
End If
End With


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"nospaminlich" <[email protected]> wrote in message
news:[email protected]...
> This is my first attempt at a Dialog Box and I'm a bit stuck.
>
> I've created a User Form which comprises a List box containing a list of
> names and a Text Box to which the User adds text then if they press OK I
want
> the value selected in the List Box to be "looked up" against a list in
cells
> A4:A13 and the text from the text box to be appended alongside that name
in
> Col B.
>
> Having designed the form I want it to run when a button is pressed on the
> sheet but I'm not sure what instructions to add to the button macro to
make
> the dialog box appear and then do what I've described above.
>
> I hope this is clear and I'd be grateful for any help. Thanks in
> anticipation.

Hi,
Hope the folloing helps. First part has a command button called
Cmd_OK on your Userform which invokes the code below when clicked. This
checks if there have been entries in the listbox and textbox; if no, it
outputs an error mesage. Otherwise it checks against A4:A13 and outputs
textbox value to corresponding cell in B.


Private Sub Cmd_OK_Click()

Dim rng As Range

If ListBox1.ListIndex = -1 Then
' No list box entry selected
MsgBox "Please select from list"
Exit Sub
Else
If TextBox1.Value = "" Then
' Text box is blank
MsgBox "Please enter value in textbox"
Exit Sub
End If
End If

Set rng = Worksheets("Sheet1").Range("A4:A13") <=== change as needed

res = Application.Match(ListBox1.Value, rng, 0)

If IsError(res) Then
MsgBox ListBox1.Value & " not found"
Else
Cells(res + 3, 2) = TextBox1.Value
End If

End Sub


On your sheet, add a command button from the Control Toolbar, right click on
button and "View Code". Add "Userform1.Show" into the macro as below (code is
in the sheet on which button resides):

Private Sub CommandButton1_Click()
UserForm1.Show <=== change if not correct Userform name
End Sub

Click the "Design Mode" button on Control toolbar (looks like triangle with
pencil/ruler) to exit design mode.

Clicking button should display Userform.

HTH

"nospaminlich" wrote:

> This is my first attempt at a Dialog Box and I'm a bit stuck.
>
> I've created a User Form which comprises a List box containing a list of
> names and a Text Box to which the User adds text then if they press OK I want
> the value selected in the List Box to be "looked up" against a list in cells
> A4:A13 and the text from the text box to be appended alongside that name in
> Col B.
>
> Having designed the form I want it to run when a button is pressed on the
> sheet but I'm not sure what instructions to add to the button macro to make
> the dialog box appear and then do what I've described above.
>
> I hope this is clear and I'd be grateful for any help. Thanks in
> anticipation.

Thanks a lot for the help. I've got it working and now I can see how it's
done tomorrow's challenge will be to create some variations on the theme.
Thanks again.