Free Microsoft Excel 2013 Quick Reference

Initializing a Userform

Hello Everyone,

I'm conducting a UserForm tutorial posted on following site:

http://www.excel-vba-easy.com/vba-us...excel-vba.html

but I can't seem to launch the form to test it. When I click on the command button that I have created to do this I recieve the error :

"Runtime Error '424' Object Required"

I can't seem to find my mistake. Can anyone offer anyhelp?

I've attached the workbook.

Thanks Everyone, you've all been great since I first signed up!


Post your answer or comment

comments powered by Disqus
Hi,

How do you automatically initialize a Userform when file has been opened?

Hi Guys,

How can you resize a userform when initializing it?

I have the code to make a userform go fullscreen, but do not know how to make it say 50% or 60% of the screen size.

I would also need the code to alter any items on the userform in accordance with the same scale as the userform resizing.

If you could assist with this one I would be really greatful.

Michael.

Hello Everyone,

How do I define a variable in order to use in the initialize part of a
userform. I am using John's Walkenbach's Help File example and I wanted the
program to default to a certain Help File based on info provided in 1 cell.
I have 4 airplanes, the airplane is selected in Cells(30,1). Based on which
airplane I select gives the topic number to start off with. This is what I
have below, but when I run it, I get errors, it doesn't like Airplane.Text,
because I don't think it is defined anywhere??. How can I read what
airplane is in the cell to select the correct topic??
(This code does NOT work)

Airplane.Text = Cells(30, 1)
If Airplane = "N2AJ" Then CurrentTopic = 1
If Airplane = "N9421D" Then CurrentTopic = 2
If Airplane = "N74NM" Then CurrentTopic = 3
If Airplane = "N146K" Then CurrentTopic = 4 Else CurrentTopic = 1

I've created a UserForm with two ListBoxes. The list entries are pulled from a named range on my worksheet. Populating the list is working fine.

My problem is with creating a default value for the ListBox. I'm doing this by assigning the .Value field. This is baffling because sometimes it works, and sometimes it does not.

My initialization code:

Private Sub UserForm_Initialize()

Dim MasterCompanyRange As Range
Dim MasterRegionRange As Range
Dim iCell As Range

'fill company list
Set MasterCompanyRange = ThisWorkbook.Names("job_table").RefersToRange.Rows(1).Cells
For Each iCell In MasterCompanyRange
    If iCell.Address = iCell.MergeArea.Cells(1, 1).Address Then
        Me.CompanyList.AddItem iCell.Value
    End If
Next iCell
'preselect 1st company in list
Me.CompanyList.Value = MasterCompanyRange.Cells(1, 1).Value

'fill region list
Set MasterRegionRange = ThisWorkbook.Names("job_table").RefersToRange.Cells(1, 1).Offset(1, 0).Resize(1,
ThisWorkbook.Names("region_count").RefersToRange.Value)
For Each iCell In MasterRegionRange
    Me.RegionList.AddItem iCell.Value
Next iCell
'preselect 1st region in list
Me.RegionList.Value = MasterRegionRange.Cells(1, 1).Value

End Sub
The UserForm also has an "Okay" button. If I tab through the ListBox before hitting "Okay", it works fine. If I accept the default value and just hit "Okay" then sometimes the ListBox passes its highlighted value into my global variable and sometimes it passes nothing. What could possibly be the difference?

In case it is relevant, here's my Okay-button click procedure:

Private Sub OkayButton_Click()

CreateJobField_Company = Me.CompanyList.Value
CreateJobField_Region = Me.RegionList.Value
Unload Me

End Sub
Any clues would be much appreciated.

I use a textbox on a UserForm to update a value in the spreadsheet. This works fine.

I would now like to initially populate the textbox with the current spreadsheet value so that the user can see the current value and only enters a value in the textbox if he doesnt like the value shown.
Is this possible or can I modify label text to display the current value ?

Hi I was wondering if anyone could help me please.

I’m making my first journey into the world of vba and userforms on Excel. I've overcome many roadblocks on my way with web tips and looking for similar problems on forums like this. (eternally grateful to you guys out there btw).

I have a userform in an Excel document that allows managers to brief in print orders for their stores. There's a dropdown menu with a selection of sizes. I want to unhide another dropdown menu if the size selected from the first drop down contains the text "Feature End".

I have tried a if argument but to no avail. This was my attempt.
The 'posType' is my first dropdown and 'feNumber' will be the dropdown I want to display if the words Feature End appear in the selection from 'posType'.


	VB:
	
 posType_Change() 
    If Me.posType.Value = "Feature End " Then 
        Me.feNumber.Visible = True 
         
    Else 
        Me.feNumber.Visible = False 
         
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This is pasted in my vba before the userform initializes. I know the above was looking for the exact text but I thought if I could identify that then I'd be able to tweak the code to just see if it contained the text string.

Any help would be very appreciated

Thanks Guys

I think my brain is not working today,
is there a way to center and position command buttons , text boxes etc of a userform once it has loaded (in the Initialize part)
well I want to have a form that is as big as the users screen (so they canot see what is happening behind. This is the code I have to do thos in the Initialize part of that form

	VB:
	
Me.Height = Application.Height 
Me.Width = Application.Width 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
now on that form I have 2 text boxes and and OK command button, and I cannot seem to get them centered since they stay in the position I put them in the form (design mode) and even if I was able to get them centered for my screen they would not on anyone elses, so I am trying to get them centered horezontaly in the initialization of the form.
IS this possible?

Hello,

I have created a couple of userforms that will initially create a new customer worksheet with the customer's ID as the worksheet name and then another that can be used to add data to the last row of a customers sheet (as the customers details, discounts, etc. change)

I now need to create a dashboard sheet that shows the latest information for each customer (the customers 'current situation') I am able to do this manually by referencing the different sheets, however, as our customer base grows it is becoming a full time task to do this.

Does anybody have any ideas on how I can have the formulas required inserted onto the dashboard sheet with the worksheet reference for the formulas taken from a textbox in a userform (The same one used to name the new customers worksheet)

Ideally I would like this to be placed in the same macro used to create the new worksheet so the dashboard entries are created at the same time as a new customer's worksheet, but any way you can think of that would speed up the process would be a great help.

Many thanks,
Colin.

I'm trying to take a list of names from a worksheet and create in a userform a combobox that lists the names.


	VB:
	
 Combo_Box_Leadoff_Click() 
    Dim counter As Integer 
    Combo_Box_Leadoff.Clear 
    For counter = 3 To 16 
        Combo_Box_Leadoff.AddItem Worksheets(5).Cells(counter, 2).Value 
    Next counter 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
In the properties window, I set the value equal to the first name in the list. The above code yields a combobox that is blank except for the value set in the properties window. When I do not set an initial value, I get a completely blank combobox.
I'm pretty new to VBA, and I've manged to figure out all of the macros I need so far, but setting up the user form has proved surprisingly difficult. I appreciate any help. Thanks a lot.

Good Afternoon All:

As shown in the sample attachted Excel spreadsheet, I have a UserForm set up to display 2 colums of the range "Elist" using a ListBox. The Userform is called by a Button.

I want my UserForm to display subsets of the "Elist" range thus restricting the users choices based on the results of a Filter operation performed on the Elist range.

I wrote some filtering code that does select my intended subset of "Elist". I then use the initialize event of UserForm to set the RowSource of the ListBox to "Elist" hoping to display the filtered subset of "Elist". However, the ListBox continues to display the full range "Elist", not the desired filter subset.

Please modify my code so it works as intented. And thanks for your help.

Cheers:

Larry Ezell

Hey Guys,
Quick problem to solve here, I have a worksheet "Master Log" with a UserForm "UserForm2" set up that has 4 comboboxes and 2 text entries. I already have the filtered unique values for each combobox sent to columns "O" thru "R" and they are dynamic ranges. I have the code to populate one of the comboboxes in the userform but cant figure out how to modify the code to have all four populated without getting ambiguous entry errors. And also, do I have to initiate the form for each combobox? Below is the code Im using for a single combobox. Thanks for the help,


	VB:
	
 UserForm_Initialize() 
    Dim MyUniqueList As Variant, i As Long 
    With Me.ListBox1 
        .Clear ' clear the listbox content
        MyUniqueList = UniqueItemList(Range("o4:o100"), True) 
        For i = 1 To UBound(MyUniqueList) 
            .AddItem MyUniqueList(i) 
        Next i 
        .ListIndex = 0 ' select the first item
    End With 
End Sub 
 
 
Private Function UniqueItemList(InputRange As Range, _ 
    HorizontalList As Boolean) As Variant 
    Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant 
    Application.Volatile 
    On Error Resume Next 
    For Each cl In InputRange 
        If cl.Formula  "" Then 
            cUnique.Add cl.Value, CStr(cl.Value) 
        End If 
    Next cl 
    UniqueItemList = "" 
    If cUnique.Count > 0 Then 
        Redim uList(1 To cUnique.Count) 
        For i = 1 To cUnique.Count 
            uList(i) = cUnique(i) 
        Next i 
        UniqueItemList = uList 
        If Not HorizontalList Then 
            UniqueItemList = _ 
            Application.WorksheetFunction.Transpose(UniqueItemList) 
        End If 
    End If 
    On Error Goto 0 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


OK, I know this is an easy question, but I can't seem to figure this one out.

I have a workbook with a UserForm, and the UserForm really has only one click event -- Calculate Rate.

After the calculations are performed, I'd like to output the data to a row on a sheet, and then increment a counter by one, so that the next calculation's data will be outputted to the next line on the sheet.

However, whenever the UserForm's click event is activated by the user, the entire sub is run through again from the beginning.

This is a problem because I've initialized the value of the counter to 13, so that the output starts on the 13th row. However, each click event parses through that initialization, and so the counter never increments -- it just gets reset to 13 each time.

I tried to initialize the counter variable at the head of the module, before any subs, but that gives me an error.

I also tried to declare the variable with "Static" rathern than "Dim" but that did not seem to help.

So, how do I initialize a variable outside of the sub in which it will be used?

How do I clear the previous selection of a listbox?

I have a UserForm, and on that form is a listbox and a textbox. The listbox has the months of the year in it for the user to select a month, and then the user enters the year in the textbox. Then, the user clicks the “Submit” button. Various code runs based on those entries.

My intention is that if the user runs that UserForm again, and forgets to click on a month, a message box will open that says “Please select a month.” However, I believe my problem is in the Initialize phase. I can’t seem to find the code to clear the listbox of the month selected during the previous use of that UserForm. Can you help?

Private Sub UserForm_Initialize()

TxtYear.Value = ""

ListMonth.Value = ""

TxtYear.SetFocus

End Sub

Upon clicking the “Submit” button, this code runs:

If ListMonth.Value = "" Then

MsgBox "Please select a month.", vbExclamation + vbOKOnly, "Invalid Input!"

Exit Sub

End If

I think the reason it does not work is because the listmonth.value does not equal "blank" when it had an entry the previous time the userform was used. How do I clear that previous entry?

Thanks for any help you can give. I’m sure it is very simple…but I have already wasted so much time on this “simple” problem.

Hi All,

I have a userform which runs a macro (which takes about 5 minutes to run) which allows the user to select a couple of variables. This all works fine, but what I want to do is display another userform while the macro is running. This userform basically just says "working... please wait" and displays a logo.

My problem is getting the new user form to show. I have it called like this...

Code:
Public Sub Proceed_Click()

On Error GoTo BadDate

Dato = DateValue(DBX)

If Dato

I hope this question can be more specific:

Below is a Code sample that works great to initialize the Event processing with Controls on a UserForm but I am stumped as to the method to use if the controls are on the Worksheet. I have tried shape, OleObjects but no joy. My application actually uses MSForms.Label placed on a worksheet so it would be nice to have only 1 click event.

Is it possible? (Thanks for any ideas)

Option Explicit
Dim Buttons() As New Class1

Sub ShowDialog()
Dim ButtonCount As Integer
Dim ctl As Control

' Create the Button objects
ButtonCount = 0
For Each ctl In UserForm1.Controls
If TypeName(ctl) = "CommandButton" Then
If ctl.Name "OKButton" Then 'Skip the OKButton
ButtonCount = ButtonCount + 1
ReDim Preserve Buttons(1 To ButtonCount)
Set Buttons(ButtonCount).ButtonGroup = ctl
End If
End If
Next ctl
UserForm1.Show
End Sub

Hi All

i have a combo box on a userform that i populate on the form initialization
event ... using the following code

dim rng as range

set rng = Sheets("LookupContainer").Range("ContainerNo")

for each cell in rng
if cell.offset(0,-1).value = "" and cell.offset(0,1).value = "" then
userform3.cboContainer.additem cell.value
end if
next

--- however when i open the userform from another userform no containers are
shown in the combo box .. if i close (cancel) and reopen it again they are -
i'm only talking about 6 records here (from about 9) ... any ideas - is
there a better way to do it? (Using excel 2000)

thanks
JulieD

How do I initialize a userform so that TextBox1 contains the value of Cell
E4 of the worksheet named Actions?

This is a silly question and should be very simple, but I'm having issues initializing a label with the concatenation of

	VB:
	
salesReportTitleLabel.Caption = "Sales report for " & Date() 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I'm not well versed in VBA at all and I have a mediocre grasp of VB.net. I imagine I have the syntax wrong or something stupid. Any help would be greatly appreciated.

How do I initialize a userform so that TextBox1 contains the value of Cell
E4 of the worksheet named Actions?

Howdy!

I have a document log that I'm creating. I have a Userform that's got all of the document's information (Sender, recipient, dates, etc...). At the bottom of the Userform however, I wish to have a table of the document's routing. (IE. Date, Reffered to, Due Date, Comments, Initials). I wish this to be the only access to users. Obviously all information is stored on a Excel sheet in background. But I want tabel with scrollbar that displays 6 routing info (No maximum) CPU add a new row when last one filled. And scroll bar use to view.
Any ideas?

Thank you,

Hello guys,

(I am a complete newbie ) I created a userform containing a multipage, a lot of checkboxes, a lot of textboxes, some listboxes, some comboboxes etc...
I want the users to have the possibility to save the entered data, and be able to continue filling the form when opening again the excel-file.
I searched forums and it is obviously not possible to save the userform, but it´s possible to transfer the data to a (hidden) worksheet and reload the data from the sheet when the userform initializes:
example of the 2 instructions: Range("A1") = NeedTextBox.Text
Userform.NeedTextBox = Range ("A1").Value

The problem is: I have so many controls that it will be really complex to write manually for each control the 2 instructions.

is there another solution?

Thanks

Hi all,

just wondering what the 'correct' way to initialize and dispose of a
userform is.

presently i am doing something like this:

private sub UserForm_Initialize()
' clear contents of combo boxes, label captions, etc
' set up initial values of the above
end sub

public sub init(optional arg as variant)
UserForm_Initialize
'any other code to run here / sub calls
MyForm.show
end sub

private sub myButton_clicked()
MyForm.hide
end sub

and externally (from another module)

MyForm.init()

is there a better (more correct) way to do this?

tia

J

Hi,

I got a problem about keeping tranfering data within a userform.

i will explain some more.
when a userform is opend the userform will collect and store date in varaibles.
when a button is clicked there will be more actions to preform.
my problem is that when there is data changed i want to reopen the userform so it takes over the new data.

is there a other way or function to instantly change the data in the labels / inputboxes without reloading the userform?

a little excample.
this is the Initialize of the userform

Public n as long
Public w as long

sub ctgWzg_Initialize ()
n = (blablabla) - w

(actions)

End sub
Button is clicked code:
Private Sub butNxt_Click ()

if n = < 8 then
w = w - 8
unload me
Load ctgWzg
else
end if

end sub
is there a way to call the Initialize subject without reloading the userform and keeping the stored data from the varaibles w & n?

i prefer avoiding putting all the same code in every clicked button subject because that will make the userform chaotic and have to reload some varaibles in each button subject.

hope im clear about my point. or please ask if i have to explain more.

tx Vin

Ok I do not know if this is possible but let me describe the setting. I have
a userform that is shown when the user presses a button on a spreadsheet. the
userform lets the user enter a start date and an end date. these dates refer
to a number of charts that are to be created by my macro. the user can
specify which charts that are to be created be selecting checkboxes, each
chekbox indicates a chart. I used to have all this on the same userform but
it created a problem beacuse the userfom got so big so it was impossible to
use it. Thus i want to change it so that the user specifies the start date
and end date on one userform and then if the user so whishes can press a
button on the first userform that displays the second userform. the second
userform lets the user choose how many charts that are to be selected. the
problem is that this needs to be optional. this means that if the user does
not choose to see the second userform the code must still work i.e. the
program chooses the default setting for the number of charts. I know how to
solve this if you simply choose a number as default. however I need to keep
it variable. my code now counts the number of checkboxes but is that possible
to do when using two userforms where the second one is optional? I do not
know if this makes any sense but please help me out if you understand me.
Thanks alot!!


No luck finding an answer? You could always try Google.