VBA Pop-up Box

porepiga

New Member
Joined
Apr 10, 2015
Messages
13
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:


Code:
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
___________________________________________________________________________________

Code:
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
_______________________________________________________________________________________

Code:
Private Sub CancelButton_Click()
Unload UserForm1
End Sub
_______________________________________________________________________________________


I Also have two modules:

MODULE 1:
Code:
Sub showForm1()
UserForm1.Show
End Sub
MODULE 2:
Code:
Sub ShowList()
UserForm1.Show
End Sub



An Anyone tell me where my mistakes are?

Thanks!!!!

Patrick
 
Last edited by a moderator:

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,724
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

New Member
Joined
Apr 10, 2015
Messages
13
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,724
That's saying a boolean value should be passed to the cell. Another way to look at it would be like this...

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

New Member
Joined
Apr 10, 2015
Messages
13
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,724
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

New Member
Joined
Apr 10, 2015
Messages
13
Correct! I want the label to show on the spread sheet.

Sorry! I'm very new at VBA.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,724
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...
Rich (BB code):
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...

Rich (BB code):
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.
 
Last edited:

Forum statistics

Threads
1,082,373
Messages
5,365,070
Members
400,821
Latest member
kezza123

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top