Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Custom Dialog Box
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Custom Dialog Box
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Custom Dialog Box
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display a Custom Dialog Box | Excel Discussion (Misc queries) | |||
Help with custom save as dialog box. | Excel Discussion (Misc queries) | |||
Custom Dialog Boxes | Excel Programming | |||
Custom View Dialog Box | Excel Discussion (Misc queries) | |||
AutoFilter Custom Dialog | Excel Programming |