Creating Custom Dialog Boxex (Popup)

[Guest]

Hello,
I am trying to create a custom dialog box that will allow me to have the
user clarify an entry when a checkbox is checked. I want them to be able to
select something from a list that I have created. I have created the popup
in Excel but cannot find out how to call the popup into the display when the
checkbox is checked. Can anyone help me please?

 

[Dave Peterson]

Is this a checkbox on the worksheet?

If yes, then if it's a checkbox from the Forms toolbar, assign this macro to the
checkbox:

Option Explicit
Sub testme()

Dim myCBX As CheckBox
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

If myCBX.Value = xlOn Then
UserForm1.Show
End If

End Sub

If it's a checkbox from the control toolbox toolbar, then double click on that
checkbox (while in design mode) and use this kind of code:

Option Explicit
Private Sub CheckBox1_Click()
If Me.CheckBox1.Value = True Then
UserForm1.Show
End If
End Sub

 

[Guest]

The check box is a VB Control from the Control Toolbox. The name of the
popup is "Popup" it is a MS Excel 5.0 Dialog form. If you could help me in
explaining the code you put in some of it I understand from programming VB
but other parts of it I am not sure why it is there and all.

 

[Dave Peterson]

Option Explicit
Private Sub CheckBox1_Click()
If Me.CheckBox1.Value = True Then
ThisWorkbook.DialogSheets("popup").Show
End If
End Sub

The checkbox control from the control toolbox toolbar has events that can be
captured. By double clicking on that checkbox, you get to the VBE and the code
window behind that worksheet.

And you get the _click event (by default).

The me. refers to the worksheet holding the checkbox.

It checks to see if the checkbox is checked. If it is, it displays the popup
dialog.

 

[Guest]

Thanks for your help so far, I would ask for a little more help with
recovering the data from the popup. The popup has a listbox and the Ok and
Cancel buttons, the listbox is populated with data from a worksheet.

when I have clicked on the listbox, the field where you can name cell ranges
shows the name List Box 5. I tried to use the in the VB code and it gave
errors. I am not sure how to recover the selected item from the list.

 

[Dave Peterson]

I think if you post the code, it would help.

Thanks for your help so far, I would ask for a little more help with
recovering the data from the popup. The popup has a listbox and the Ok and
Cancel buttons, the listbox is populated with data from a worksheet.

when I have clicked on the listbox, the field where you can name cell ranges
shows the name List Box 5. I tried to use the in the VB code and it gave
errors. I am not sure how to recover the selected item from the list.

 

[Guest]

This is the code for the 5 check boxes that can bring up the popup

Private Sub chkW1TwoWeap_Click()
Weapon = 1
If Me.chkW1TwoWeap.Value = True Then
ThisWorkbook.DialogSheets("popup").Show
End If
End Sub

Private Sub chkW2TwoWeap_Click()
Weapon = 2
If Me.chkW2TwoWeap.Value = True Then
ThisWorkbook.DialogSheets("popup").Show
End If
End Sub

Private Sub chkW3TwoWeap_Click()
Weapon = 3
If Me.chkW3TwoWeap.Value = True Then
ThisWorkbook.DialogSheets("popup").Show
End If
End Sub

Private Sub chkW4TwoWeap_Click()
Weapon = 4
If Me.chkW4TwoWeap.Value = True Then
ThisWorkbook.DialogSheets("popup").Show
End If
End Sub

Private Sub chkW5TwoWeap_Click()
Weapon = 5
If Me.chkW5TwoWeap.Value = True Then
ThisWorkbook.DialogSheets("popup").Show
End If
End Sub

This is the code I tried in the popup coding to return the value of what was
chosen

Sub dialog1_unload()
Select Case Weapon
Case 1
Range("'Weapons and Armor'!$D$18").Select
ActiveCell.Value = popup.Listbox5.Value
Case 2
Range("'Weapons and Armor'!$G$18").Select
ActiveCell.Value = popup.Listbox5.Value
Case 3
Range("'Weapons and Armor'!$J$18").Select
ActiveCell.Value = popup.Listbox5.Value
Case 4
Range("'Weapons and Armor'!$M$18").Select
ActiveCell.Value = popup.Listbox5.Value
Case 5
Range("'Weapons and Armor'!$P$18").Select
ActiveCell.Value = popup.Listbox5.Value
End Select
End Sub

 

[Dave Peterson]

I put this in a general module:

Option Explicit
Public weapon As Long

Sub dialog1_unload()

Dim myPopup As DialogSheet
Dim myString As String

Set myPopup = ThisWorkbook.DialogSheets("Popup")

If myPopup.ListBoxes("listbox5").Value < 1 Then
MsgBox "None selected!"
Exit Sub
End If

With myPopup.ListBoxes("listbox5")
myString = .List(.ListIndex)
End With

With Worksheets("Weapons and Armor")
Select Case weapon
Case 1
.Range("$D$18").Value = myString
Case 2
.Range("$G$18").Value = myString
Case 3
.Range("$J$18").Value = myString
Case 4
.Range("$M$18").Value = myString
Case 5
.Range("$P$18").Value = myString
End Select
End With

End Sub

(And kept your code under the sheet with the checkboxes.)

The listbox from the Forms toolbar (like the one you used on the Dialog sheet
doesn't return the value of the selected item. It returns an index into that
list. So myString looks at that item in the list.

 

[Guest]

I had to put this project on the back burner for a bit but am now bat at it.
I used your code and recieved an error
"Unable to get the Listboxes property of the DialogSheet class"
To be honest I am not sure what that means. I am used to being able to name
every control and capture their events but the popup doesn't seem to do this
and it is confusing me a bit (and the help files are not very forthcomming
with info on it either)

 

[Dave Peterson]

Are you sure it's a listbox?

Are you sure you've got the correct dialogsheet?

(My only guesses...)