Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Initializing userforms Results

I have a userform that has one combobox and 3 textboxes. And when a selection is made from the combobox then the 3 textboxes are populated with text. Problem is that when the userform is initialize and the user makes a selection then only the first textbox is being populated but if the user makes another selection then it starts working correctly. While trying to debug it i a msgbox after each textbox line of code and it works fine. Its almost like the program is running to quickly and needs to be paused after populating each textbox. And ideas what is causing this and how to fix it?

Private Sub ComboBox1_Change()

If ComboBox1 = "mark" Then
TextBox1 = "one"
TextBox2 = "two"
TextBox3 = "three"

End If

If ComboBox1 = "chuck" Then
TextBox1 = "one"
TextBox2 = "three"
TextBox3 = "five"
End If

End Sub

i have a userform that is a little busy upon activation

so when it opens the screen 'whites out' for a few seconds and then the form comes in (non-modal)

is there a way to launch a form and not have it show until all the initializing/activating stuff is complete?

Trying to debug some userform code but when I use step into it will step through the Initialize code only and then show the userform. It will not step through any other code associated with the userform. Is Excel corrupt? Is this just the way it works? Im I doing or not doing something necessary? Thanks

Hi,

I am writing a macro that calls a COM utility that takes a couple of minutes to do a calculation. E.g., my macro has code like

dim a as MyComObject
set a = new MyComObject
' some code here to initialize a
a.property1 = 5
a.property2 = 6
'etc
'now tell a to do its calculation
a.run 'this takes a long time

When I execute the last command, Excel displays an hourglass for 2-3 minutes. I'd like to be able to display something (could be in a spreadsheet cell, or in a userform, or anywhere) that flashes a message like:

a is still calculating! it's been calculating for 10 seconds!

Then:

a is still calculating! it's been calculating for 20 seconds!

etc.

The purpose for this is to placate the user, so I am very flexible in terms of what I would display and how I would display it--if, for example, I can't accurately time 10 seconds, 20 seconds, etc, that is ok--it would be useful to have _something_ changing on the screen while the COM utiltity is running.

Thanks in advance!

i have a combobox and several textboxes. and when a selection is made from the combobox it puts text in the textboxes and then the using the controlsource property it sends that text to a spreadsheet. problem is when the program is first open and userform initialized it only works the first 2 textboxes, but if you make another selection everything works fine from then on. i also realized while debugging it that when i popped a msgbox after each line of code it seemed to work fine. so does that mean thats too many steps (combobox to textbox to cell)? see my below code.

If frm.cboTiers.Text = "3" Then
frm.txtCov1 = "Emp"
frm.txtCov2 = "2 Person"
frm.txtCov3 = "Family"
frm.txtCov4 = ""
frma.txtCov5 = ""
frm.txtCov6 = ""
End If

Hi everyone

I'm trying to set up a generic method to populate the values of a certain range in a worksheet to a combo box on a userform (in this case called cboEmployeeNames). As the project will require me to repeat this process for several combo boxes it just made sense to write a procedure that would make this a less paintful experience and being a Noob at VBA this is my attempt so far. Everything seems to work fine untill I call the function when the userform is initialized.

Code:
 
    populateCombo(cboEmployeeNames,"Employee Name","Employees")  
 
End Sub
This returns an error and is looking for an expected = which I assume has something to do with the fact that it has no idea what the combobox parameter is referring to and I'm so code blind that I cannot figure this out. comboName = ComboBox?????

Code:
 
Public Sub populateCboGeneral(comboName As ComboBox,FieldName as String, sheetName as String)
 
    LastRow = mdMain.getFirstEmptyRow(sheetName)
    j = mdMain.FindFieldIndex(FieldName,sheetName) 
 
    For i = 2 To LastRow - 1 'First row contains the FieldNames)
        If Worksheets(sheetName).Cells(i, j)  Worksheets(sheetName).Cells(i + 1, j) Then
            comboName.AddItem (Worksheets(sheetName).Cells(i, j))
        End If
    Next
 
End Sub
Any help would be appreciated.

Thanks

Hi

Please excuse my ignorance.

I have a sheet which was working fine, but now the userform wont initialize.

It is listed under Forms and the property window has the correct name receiptEntry

Sub dataentry()
receiptEntry.Show
blok = False
End Sub

The sheet works fine until this sub is called then I get the 'Path Not Found' Error with receiptEntry.Show Highlighted

I also get run time error '76' Path not found if I try to F5 through the form code.

Please help

Thanks Duncan

Hi-
Im trying to enter the value of a cell into a textbox on a userform. This is the code that initializes when the userform is shown:

Private Sub UserForm_Initialize()

Sheets(TextBox3.Text).Activate
ActiveSheet.Visible = True

[e7000].End(xlUp).Offset(0, 0).Select
TextBox2.Text = Range.Offset(0, 0).Value

End Sub

The value of the cell that is selected using the line:[e7000].End(xlUp).Offset(0, 0).Select . This finds the cell that im looking for, but I cant seem to figure out how to list it in textbox2. As you can see Ive tried a code(tried many other ways also) to show the value, but its not working. Perhaps I should use the offset methode, but im not sure how to apply it to this code. Please help?

hi everyone, first post here, i have a very basic knowledge to excel and vb but i've done ok up to this point.

basically, i am trying to create an excel template, this template will have a userform that users will enter certain details, e.g. initials/firstname and also version control (i.e. v1.0.0) which will then be inputted into the left footer box.

i have written the vb script that will write to that footer area but because it is multiple textboxes/values i don't know the correct way in which to add together as one value them.

in the userform there are 4 textboxes (txtDate, txtVersion, txtName, txtSurname). i need the footer to look as follows:
Copyright © / txtDate / txtName txtSurname / txtVersion

the script for adding the data is as below:

Private Sub cmdOk_Click()

ActiveWorkbook.Unprotect Password:="password"

'Sets string as range
Dim strProtective As String

' inserts the same header/footer in all worksheets
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Changing header/footer in " & ws.Name

With ws.PageSetup
.LeftHeader = cbosecurity.Value
.RightHeader = strProtective
.LeftFooter =
End With
Next ws
Set ws = Nothing

Application.StatusBar = False

ActiveWorkbook.Protect Password:="password"
Unload Me

End Sub

I hope this makes sense, any help would be gratefully received.

Hi all,

I have a combo box on a userform that is populated using "Add item". I would like to be able to make one of the values the default value that will show in the combo box when the userform is initialized.

Regards,

Mike

Hello!

I'm actually very new to building macros using VBA, so any and all help is much appreciated. I was able to follow instructions, in the Excel 2002 Power Programming with VBA book, to create a UserForm progress bar. My issue is that I'm realizing that a macro is needed to initiate the start of the progress bar, however, what I need is the progress bar to appear at the same time Excel calculates the formulas in my sheet. Basically, I would like the progress bar to appear in tandem with the Excel Status Bar (ie. Ready, Calculating, etc). I normally would not be worried about it, however, my clients don't usually notice Excel's status bar at the bottom when formulas are calculating and I'm afraid they might start to click around in my file and mess something up.

Ultimately, I would like to make the Excel Status bar more prominent via a User Form Progress Bar, if that makes sense.

Any help is appreciated.

Thanks so much!

Hi All

I am looking for ideas and examples on how to do the following.

I have an add-in which after loading the user can open a UserForm which loads VB Modeless. On the UserForm I want to add either a Checkbox or OptionButton (not sure which yet it depends on what works best). Whichever I opt for I need the user to select it. Once selected the following needs to happen:

When a cell is selected on the active sheet the used range below it is selected i.e. If I select column 2 and lets say I select B1 and the used range is it B1:B100 then B2:B100 would be selected. So the initial cell selected remains unselected and the used range below it is selected if that makes sense. The purpose of this is to select a title in a header row and by doing so select the used range below it rather than dragging the mouse down.

I need to do this only as long as the check or option button is checked on the userform. If the check or option button is unchecked then selecting a cell will only select a cell.

Any thoughts or ideas would be appreciated.

I've got a userform, with a couple of comboboxs, which populate on initialize with the column headers (row 1) of the active sheet.

I'd like it so that certain comboboxes will only contain those column headers where the column contains recognised dates, and for others to only contain columns which contain numerical data.

Unfortunalty there are some errors in the source data that will be used, which may mean that the odd numerical or date field contains invalid data. To combat this I'd like it so that if (say) 80% of the non-blank cells in a column are recognised as a date then that column is classed as a date column (and the same for numnerical columns).

The only way I can think of doing this is to go through each cell in each column, and use a counter and ISDATE() to "manually" count up each cell and each cell with a date in it, then divide one by the other - if the result is bigger than 0.8 then add the column to the combobox.

However, this is a time consuming and cumbersome way of going about this.

Anyone got any better ideas?

Cheers

Charlie

I am using a user form where I want that If the user checks the Chkbox1, then txtbox1,txtbox2 nad txtbox3 should be shown, else txtbox4,txtbox5 should be visible.. This I am doing by setting the visible property as true and false based on chkbox value.

But this doesnot adjust the Userforms size.

Can a solution be provided tso that at initialization, the size of the form will be reduced to visible controls and then expand as per choice.

I have a userform that looks at the Footer and manipulates it for ISO documents. How can I tell what the font size is when I initialize the form?

Code:
Dim LF as string
'Tells me what in the footer but not font size
LF = activesheets.pagesetup.leftfooter
I've tried .font.size but it has errors: Object Required

I don't know how to fix it?
Help!
dforgacs

I have a multiselct listbox on a userform displaying names of all the sheets in a workbook. I want to hide all of the selected names in the listbox on a commandbutton click.

The code showing how I'm populating the listbox initially and then the routine for hiding the selected sheet names is below:

Code:
Private Sub UserForm_Initialize()

    Dim i As Long

'   Populate Listbox with Sheet names
    With ActiveWorkbook
        For i = 1 To .Sheets.Count
            lb_Hide_Sht.AddItem .Sheets(i).Name
            lb_Unhide_Sht.AddItem .Sheets(i).Name
        Next i
    End With

'   Hide everything but the initial option buttons
    fr_Hide_Sht.Visible = False
    fr_Hide_Rng.Visible = False
    fr_Unhide_Sht.Visible = False
    fr_Unhide_Rng.Visible = False

End Sub

Sub HideSheets()
'   September 2007

'   Hides all selected sheets

    Dim i As Long
    
    With lb_Hide_Sht
        For i = 1 To .ListCount
            If .Selected(i - 1) Then
                ActiveWorkbook.Sheets(.List(i)).Visible = xlSheetHidden
            End If
        Next i
        
        .Clear
    End With
    
    With ActiveWorkbook
        For i = 1 To .Sheets.Count
            If .Sheets(i).Visible = xlSheetVisible Then lb_Hide_Sht.AddItem .Sheets(i).Name
        Next i
    End With

End Sub
The problem I'm having is that the .Selected() array seems to be in a different order than the .List() array -- that is, if there are 3 sheets in the listbox, and the user highlights just the first one in the list, the program actually hides the last sheet.

What am I missing?
Ben.

i have a form with a combobox that will need to be populated with 150 cities. Is there a way to get the combobox to refer to a list in the spreadsheet cells or do i have to put 150 individual lines: combobox1.additem "Los Angeles"
combobox1.additem "San Diego"
in the userform initialization module.

My question has two parts:

1. In a custom Userform with multipage object, can the page change event of the Multipage be used to initiate a vba procedure?

2. If so, is it possible to use this event to populate the page with a number of textbox objects (never more than 10) based on a value entered by the user into a text box on page 1 of the Multipage?

I have been working on a VBA app all day. Have been running code and initializing a large userform with no problems until recently. Now I am getting a "Path/File access error" when i try to show the form. Not being able to find a reason for this error, I was thinking I could export all of my code/forms/modules, close the file and re-open it to import the modules and forms to relieve my problems.

Is it possible to automate the export of modules through code? I know I can remove them all through code.

Or if somebody has a better idea as to how to fix this issue.....I'm all ears!

Hi,
Earlier this week, I asked for help on progress bars and got some great suggestions, one being to review previous postings on this subject which I did and got the following code which I can get to work fine..........but I dont know how to insert this code into one of my macros.

Can anyone give me a few tips on what I need to do, my macro moves between a number of different sheets, does some sorting, deleting, and copying, how and where do I put this code in the macro.

Any help would be greatly appreciated,

Thanks

Steve

Sub ShowUserForm()
UserForm1.Show
End Sub

Sub Main()
Dim Counter As Integer
Dim RowMax As Integer, ColMax As Integer
Dim r As Integer, c As Integer
Dim PctDone As Single

Application.ScreenUpdating = False
' Initialize variables.
Counter = 1
RowMax = 100
ColMax = 25

' Loop through cells.
For r = 1 To RowMax
For c = 1 To ColMax
'Put a random number in a cell
Cells(r, c) = Int(Rnd * 1000)
Counter = Counter + 1
Next c

' Update the percentage completed.
PctDone = Counter / (RowMax * ColMax)

' Call subroutine that updates the progress bar.
UpdateProgressBar PctDone
Next r
' The task is finished, so unload the UserForm.
Unload UserForm1
End Sub

Sub UpdateProgressBar(PctDone As Single)
With UserForm1

' Update the Caption property of the Frame control.
.FrameProgress.Caption = Format(PctDone, "0%")

' Widen the Label control.
.LabelProgress.Width = PctDone * _
(.FrameProgress.Width - 10)
End With

' The DoEvents allows the UserForm to update.
DoEvents
End Sub


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