VBA Pop-up Box

[porepiga]

Hello!

I'm trying to create a pop-up window using VBA. I've been using a post by Trevor G and Lisa1, and I belive I've gotten quite ahead on this, but I can't conclude my window, as this is a bit more complex than the one on the mentioned post.

Here's what I have so far:

Private Sub cmdOK_Click()
Sheets("Sheet2").Select
Range("A2").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(0, 1).Value = Me.txtListBox1.Value
ActiveCell.Offset(0, 2).Value = Me.txtFrame1.Value
ActiveCell.Offset(0, 3).Value = Me.txtCheckBox1.Value
ActiveCell.Offset(0, 4).Value = Me.txtTextBox1.Value
ActiveCell.Offset(0, 5).Value = Me.txtTextBox2.Value
ActiveCell.Offset(0, 6).Value = Me.txtTextBox3.Value
ActiveCell.Offset(0, 7).Value = Me.txtTextBox4.Value
ActiveCell.Offset(0, 8).Value = Me.txtFrame2.Value
ActiveCell.Offset(0, 9).Value = Me.txtTextBox5.Value
ActiveCell.Offset(0, 10).Value = Me.txtTextBox8.Value
ActiveCell.Offset(0, 11).Value = Me.txtTextBox11.Value
ActiveCell.Offset(0, 12).Value = Me.txtTextBox12.Value
ActiveCell.Offset(0, 13).Value = Me.txtTextBox6.Value
ActiveCell.Offset(0, 14).Value = Me.txtTextBox7.Value
ActiveCell.Offset(0, 15).Value = Me.txtTextBox9.Value
ActiveCell.Offset(0, 16).Value = Me.txtTextBox10.Value


Me.Hide
End Sub

___________________________________________________________________________________

Private Sub UserForm_Initialize()
' Fill the list box
With ListBox1
.AddItem "PST"
.AddItem "PT"
.AddItem "AUTO"
.AddItem "GSU"
.AddItem "REAC"
End With


' Select the first kist item
ListBox1.ListIndex = 0


End Sub

_______________________________________________________________________________________

Private Sub CancelButton_Click()
Unload UserForm1
End Sub

_______________________________________________________________________________________


I Also have two modules:

MODULE 1:

Sub showForm1()
UserForm1.Show
End Sub

MODULE 2:

Sub ShowList()
UserForm1.Show
End Sub

An Anyone tell me where my mistakes are?

Thanks!!!!

Patrick

 

[Momentman]

Whats the problem exactly?

 

[Zack Barresse]

Why are you hiding the userform? It's still loaded if you just hide it, which isn't all that important as long as you know 1) it's still running in-memory, and 2) the initialize code will only run once. If you want to truly destroy it when done (generally this is what's done) you would use "Unload Me" instead of "Me.Hide".

 

[porepiga]

Thank you for responding. At this time the problem I'm having is with the option buttons:

ActiveCell.Offset(0, 8).Value = Me.OptionButton1.Value = True

When the information is transfered to the Excel sheet, "True" shows up, but I want the value of the button to show instead.

Thanks for helping out

 

[Zack Barresse]

That's saying a boolean value should be passed to the cell. Another way to look at it would be like this...

ActiveCell.Offset(0, 8).Value = (Me.OptionButton1.Value = True)


'or...


ActiveCell.Offset(0, 8).Value = Me.OptionButton1.Value


'or...


ActiveCell.Offset(0, 8).Value = CBool(Me.OptionButton1.Value = True)

An option button Value property will either be True or False, whether it's selected or not. So you're getting exactly what you're asking for. My only confusion is where you say you want the "value of the button to show instead." What button are you talking about? We need more information.

 

[porepiga]

Sorry I wasn't more explicit. I've assigned a value to each one of the buttons I have in the frame: 30, 45, 60, 90, 100. This is the value I need on the Excel sheet.

Should I use a "Dialogue Box" instead?

Thank You for your help.

 

[Zack Barresse]

Still not clear. Do you mean you've changed the text of the Option Button's, and you want to return it's label?

Do this, select the control you're talking about and press F4 to show the properties pane. It will tell you the type of control and you can look through the properties and find what you're looking for. I'm assuming you're talking about it's Label and not Value property. ?

 

[porepiga]

Correct! I want the label to show on the spread sheet.

Sorry! I'm very new at VBA.

 

[Zack Barresse]

No worries about being new. That's what we're here for.

For option buttons it's the Caption, not Label, sorry. Should be something like this...

ActiveCell.Offset(0, 8).Value = Me.OptionButton1.Caption

Edit: in retrospect, are you saying you want to get the caption of the selected option button? If so we would need to see which one is selected or not. So if you have 5 option buttons, you could do something like this...

If Me.OptionButton1.Value Then ActiveCell.Offset(0, 8).Value = Me.OptionButton1.Caption
If Me.OptionButton2.Value Then ActiveCell.Offset(0, 8).Value = Me.OptionButton2.Caption
If Me.OptionButton3.Value Then ActiveCell.Offset(0, 8).Value = Me.OptionButton3.Caption
If Me.OptionButton4.Value Then ActiveCell.Offset(0, 8).Value = Me.OptionButton4.Caption
If Me.OptionButton5.Value Then ActiveCell.Offset(0, 8).Value = Me.OptionButton5.Caption

If you have an unknown number of option buttons we could always do a loop, or even write a separate function for it, but we'd need more information on it.

 

[porepiga]

Thank you so much! That worked out just perfectly!

Best Regards