Execute code from auto generated buttons in a userform

**Tellus** · 08-27-2008, 07:16 AM

I soon don't have any hair left, so please help me out...

I have 3 Regions with a number (different between regions) of companies. Via a userform i would like the user to state how many employees each company holds and save this information back to the spreadsheet.

The attached file descibes my problem.

1. Via a ComboBox Region A, B or C is choosen.

2. By clicking the Indata-button the userform isshown. Since each region have different number of companies (cells F33:G54), the userform will have different number of Labels and TextBoxes depending on which region that is choosen.

When the userform initiates, cells C15:C17, are used to decide what information will be shown in the userform. The Cancel-button and Save-button are also created when the userform initiates.

By filling in number of employees and clicking the Save-button, the number of employees should be written to cell H34:H54.

By clicking the Cancel-button, the userform should just be unloaded.

I can not get this to work!

1. How do I get the Cancel-button and Save-button to work? I can not get them to execute any code.
2. How do I get the information in the textboxes (number of employees) to be written into the worksheet.

Any ideas on how to solve this are more then wellcome!
/Fredrik

**Andy Pope** · 08-27-2008, 07:36 AM

Rather than add those 2 button via code do it at design stage. Code against the events as normal.
Then when Initialising the userform re-position the 2 buttons.

You could set the ControlSource property of the textbox, although this does not allow for the Cancelling of changed values.

So maybe better to load the textbox as you create it. And code in the OK click event would loop through the controls you added and output the text to the appropriate cell.

**Tellus** · 08-27-2008, 08:07 AM

Re-positioning the buttons was just so clever. Why make things complicated? Thanks a lot!

The problem with transfering data from the text boxes still remain though.

When adding textboxes in design-mode I can refer to them in a way like this:

Activesheet.Range("H34")=Employees1.value

Since the text boxes are created at userform-initiation (Employees1...Employeesn), I can not refer to them when clicking the Save-button,

In the userform initiation I have this code:

For i = StartRow To StartRow + NoCompanies - 1

    If Worksheets("Start").Cells(i, 8) = "" Then
        Number = "n/a"
    Else
        Number = Worksheets("Start").Cells(i, 7)
    End If
    
    Set tNumber = FormIndata.Controls.Add("Forms.TextBox.1")
        With tNumber
            .Value = Number
            .Width = 50
            .Top = j - 3
            .Left = 150
            .Height = 17
            .TextAlign = fmTextAlignRight
            .Name = "Employees" & i - StartRow + 1
        End With
            
    j = j + 20
Next i

Any thoughts on how to refer to the textboxes?

/Fred

**Andy Pope** · 08-27-2008, 08:22 AM

Use something like this.

For i = StartRow To StartRow + NoCompanies - 1

    Worksheets("Start").Cells(i, 8) = Me.Controls("Employees" & i).Text
Next

**Tellus** · 08-27-2008, 09:07 AM

Thanks Andy!

Finaly it's working and my day is saved!

Execute code from auto generated buttons in a userform

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Execute code from auto generated buttons in a userform

Thread Information

Users Browsing this Thread

Similar Threads

Adding code to a form on the fly...

To execute code before XL loads.

Removing command buttons using code

Muultiple userform 'reset' code question

UserForm Code Question/Help

Bookmarks

Bookmarks

Posting Permissions