Problem with - UserForm Initialize

E

excelenergy

Board Regular
Joined
Jun 7, 2012
Messages
142
Hello

I have a command button, located on my spreadsheet. The code for the command button is in the vba editor within the name of the sheet - which is sheet114.

When the command button is clicked, a userform pops up. The userform has combo-boxes on it.

The userform is failing to initialize, and populate the combox boxes. Here is the code for the user-form initialize sequence and the drop downs, I was curious why isn't it initializing and populating? It should be, I'm not getting any kind of errors from VBE.

Code for the Command Button:

Code: 
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub





Code for Initialization of the Form:
Code: 
Private Sub UserForm1_Initialize()
With ComboBox1
 .AddItem "Telephones"
 .AddItem "Computers"
 .AddItem "Monitors"
End With
End Sub




This is the code for combo-box1:

Code: 
Private Sub ComboBox1_Change()
Dim index As Interger
 index = ComboBox1.ListIndexComboBox2.Clear

Select Case index
     
Case Is = 0
With ComboBox2
.AddItem "CISCO
.AddItem "Bell"
.AddItem "Oracle"
        End With
     
     
     Case Is = 1
         With ComboBox2
.AddItem "Sony"
.AddItem "Hewlet Packard"
.AddItem "Dell"
End With

Case Is = 2
With ComboBox2
.AddItem "Acer"
.AddItem "Dell"
.AddItem "Samsung"
End With

End Select
End Sub
 
Sort by date Sort by votes
excelenergy said:
Hello


This is the code for combo-box1:

Code: 
Private Sub ComboBox1_Change()
Dim index As Interger
Click to expand...


Well, I haven't looked into this issue much yet, but Integer is spelled wrong here ^ I'll keep reading though

EDIT: Also, missing quotation marks after CISCO
 
Last edited:
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
At the top of the Userform module there are 2 dropdowns.
Click the arrow on the left hand one & you will get a list of all the controls on the form, do you see ComboBox1 in there? (scroll down if needed)
 
Upvote 0
Hey

1. I fixed the integer spelling problem

Okay, so in my list in VBE, I have ComboBox1, ComboBox2, Command Button1, Label 1, Label 2, Label 3 and Userform.

When I I click combo box 1 in the VBE...then in the drop down next to the right has your standard:
Click
Change
DblClick
Enter
Error

...and so on..


Fluff said:
At the top of the Userform module there are 2 dropdowns.
Click the arrow on the left hand one & you will get a list of all the controls on the form, do you see ComboBox1 in there? (scroll down if needed)
Click to expand...
 
Upvote 0
I recreated your form and copy/pasted your code.

With three revisions, mine began working.

(Changed Spelling of Integer, added line break to the next line, added quotation marks to CISCO)

Ends up looking like this:

Code: 
Private Sub UserForm_Initialize()
With ComboBox1
 .AddItem "Telephones"
 .AddItem "Computers"
 .AddItem "Monitors"
End With
End Sub

Code: 
Private Sub ComboBox1_Change()
Dim index As Integer
 index = ComboBox1.ListIndex
 ComboBox2.Clear


Select Case index
     
Case Is = 0
With ComboBox2
.AddItem "CISCO"
.AddItem "Bell"
.AddItem "Oracle"
        End With
     
     
     Case Is = 1
         With ComboBox2
.AddItem "Sony"
.AddItem "Hewlet Packard"
.AddItem "Dell"
End With


Case Is = 2
With ComboBox2
.AddItem "Acer"
.AddItem "Dell"
.AddItem "Samsung"
End With


End Select
End Sub
 
Last edited:
Upvote 0
I tried it...Except, I'm not sure if I did the line break correctly, just shift>enter?
Here is a shared link to the downloadable file
https://drive.google.com/open?id=18ECmIRV73wcOQQN3-_e1-uN0Z9zA5UEN


Also - Are you putting all of your code into the Module, or the userform? Because right now I have my code split out...The code for the Userform_initialize() and Combo Box1 is in UserForm1

The Sub for the Command button is on the page named 'Sheet1' in the VBE...

QUOTE=hotabae;5054223]I recreated your form and copy/pasted your code.

With three revisions, mine began working.

(Changed Spelling of Integer, added line break to the next line, added quotation marks to CISCO)

Ends up looking like this:

Code: 
Private Sub UserForm_Initialize()
With ComboBox1
 .AddItem "Telephones"
 .AddItem "Computers"
 .AddItem "Monitors"
End With
End Sub

Code: 
Private Sub ComboBox1_Change()
Dim index As Integer
 index = ComboBox1.ListIndex
 ComboBox2.Clear


Select Case index
     
Case Is = 0
With ComboBox2
.AddItem "CISCO"
.AddItem "Bell"
.AddItem "Oracle"
        End With
     
     
     Case Is = 1
         With ComboBox2
.AddItem "Sony"
.AddItem "Hewlet Packard"
.AddItem "Dell"
End With


Case Is = 2
With ComboBox2
.AddItem "Acer"
.AddItem "Dell"
.AddItem "Samsung"
End With


End Select
End Sub
[/QUOTE]
 
Upvote 0
Just for the fun of it, I tried re-creating it...and still got the same error in the same place... Run-time Error 424 - Object Required...and in yellow Userform.show gets highlighted in yellow


excelenergy said:
I tried it...Except, I'm not sure if I did the line break correctly, just shift>enter?
Here is a shared link to the downloadable file
https://drive.google.com/open?id=18ECmIRV73wcOQQN3-_e1-uN0Z9zA5UEN


Also - Are you putting all of your code into the Module, or the userform? Because right now I have my code split out...The code for the Userform_initialize() and Combo Box1 is in UserForm1

The Sub for the Command button is on the page named 'Sheet1' in the VBE...

QUOTE=hotabae;5054223]I recreated your form and copy/pasted your code.

With three revisions, mine began working.

(Changed Spelling of Integer, added line break to the next line, added quotation marks to CISCO)

Ends up looking like this:

Code: 
Private Sub UserForm_Initialize()
With ComboBox1
 .AddItem "Telephones"
 .AddItem "Computers"
 .AddItem "Monitors"
End With
End Sub

Code: 
Private Sub ComboBox1_Change()
Dim index As Integer
 index = ComboBox1.ListIndex
 ComboBox2.Clear


Select Case index
     
Case Is = 0
With ComboBox2
.AddItem "CISCO"
.AddItem "Bell"
.AddItem "Oracle"
        End With
     
     
     Case Is = 1
         With ComboBox2
.AddItem "Sony"
.AddItem "Hewlet Packard"
.AddItem "Dell"
End With


Case Is = 2
With ComboBox2
.AddItem "Acer"
.AddItem "Dell"
.AddItem "Samsung"
End With


End Select
End Sub
Click to expand...
[/QUOTE]
 
Upvote 0
The commandbutton code should be
Code: 
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
And along with the corrections mentioned by hotabae, you should be ok.
 
Upvote 0
Hey,

Alright, so that got rid of the error. Thank you!! But after the userform pops up - the boxes still don't populate.
I made all the changes - except, I might not have inserted the line break correctly I'm pretty sure I did though.

Fluff said:
The commandbutton code should be
Code: 
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
And along with the corrections mentioned by hotabae, you should be ok.
Click to expand...
 
Upvote 0
Fluff Has it covered but to answer your earlier questions:

I tried it...Except, I'm not sure if I did the line break correctly, just shift>enter?
Click to expand...

That would be fine, but a simple 'enter' would suffice. Just needed it on a new line.

Also - Are you putting all of your code into the Module, or the userform? Because right now I have my code split out...The code for the Userform_initialize() and Combo Box1 is in UserForm1
Click to expand...

All of this code was stored in the userForm (except the initial call button - commandButton1).
 
Upvote 0
The userform code should be
Code: 
Private Sub UserForm_Initialize()
   With ComboBox1
    .AddItem "Telephones"
    .AddItem "Computers"
    .AddItem "Monitors"
   End With
End Sub

Private Sub ComboBox1_Change()
   Dim index As Integer
   index = ComboBox1.ListIndex
   ComboBox2.Clear
   
   Select Case index
      Case Is = 0
         With ComboBox2
            .AddItem "CISCO"
            .AddItem "Bell"
            .AddItem "Oracle"
         End With
      Case Is = 1
         With ComboBox2
            .AddItem "Sony"
            .AddItem "Hewlet Packard"
            .AddItem "Dell"
         End With
      Case Is = 2
         With ComboBox2
            .AddItem "Acer"
            .AddItem "Dell"
            .AddItem "Samsung"
         End With
   End Select
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
159
krunal123
K
J
  • Question Question
Userform inconsistent responses
Replies
1
Views
168
Candyman8019
C
K
  • Solved
VBA in User Form. Xlookup formula for Dependent Combo Box.
Replies
8
Views
226
Kscoof
K
I
  • Question Question
Listbox not being populate when code is run
Replies
4
Views
630
ipbr21054
I
D
  • Question Question
why show the frame of userform as the photo1
Replies
0
Views
197
david2005wang
D

Forum statistics

Threads
1,223,262
Messages
6,171,080
Members
452,377
Latest member
bradfordsam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top