generate a form at runtime?

  • Thread starter Thread starter JJ
  • Start date Start date
J

JJ

Ok. Heres what i want to do.

I have a variable number of sheets with variable names.

I want to at runtime produce a form which lists all the sheets and a
check box for each one.

this is for my users to select which of there sheets they want to run
the macro on.

Can any one point me in the right direction ?

Or is there a better way to do this?

Thanks in advance!

J
 
A combobox is the usual way of doing this. And a whole lot easier that
creating check boxes on the fly.

--
Jim
| Ok. Heres what i want to do.
|
| I have a variable number of sheets with variable names.
|
| I want to at runtime produce a form which lists all the sheets and a
| check box for each one.
|
| this is for my users to select which of there sheets they want to run
| the macro on.
|
| Can any one point me in the right direction ?
|
| Or is there a better way to do this?
|
| Thanks in advance!
|
| J
|
 
Problem with creating a checkbox on the fly is that you also have to
generate some code to process them on the fly.

Far better IMO to add the max number of checkboxes you will need and hide
them, unhiding as required, or to use another method, such as dropping the
sheet names in a listbox or a combobox, and process the selected sheet.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Rather than adding controls consider a listbox with the properties

ListStyle = fmListStyleOption
MultiSelect = fmMultiSelectMulti

Cheers
Andy
 
JJ, I've used this approach to make dynamic checkboxes
Dim ctlCheckBox As Control
For cnt = 1 to 10
Set ctlCheckBox = UserForm1.Controls.Add _
("Forms.Checkbox.1", "ctlCheckBox" & cnt)
ctlCheckBox.Caption = ActiveWorkbook.Worksheets(cnt).Name
Next

then call back the info like this:
For cnt = 1 to 10
MsgBox UserForm1.Controls("ctlCheckBox" & cnt).Value
Next
 
and code them like how?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
In this case the user doesn't need code behind them. He just needs to use a
command button to launch the macro and have the macro check the state of the
checkboxes. Normally I would make individual checkboxes and just hide them
but there could be a couple hundred sheets and placing all those checkboxes
would suck. Anyhow if you want to put code behind them can't you just use
j-walk's code for coding multiple buttons?
(http://j-walk.com/ss/excel/tips/tip44.htm)
 
Yes you can.

--
Regards,
Tom Ogilvy

Charles Chickering said:
In this case the user doesn't need code behind them. He just needs to use
a
command button to launch the macro and have the macro check the state of
the
checkboxes. Normally I would make individual checkboxes and just hide them
but there could be a couple hundred sheets and placing all those
checkboxes
would suck. Anyhow if you want to put code behind them can't you just use
j-walk's code for coding multiple buttons?
(http://j-walk.com/ss/excel/tips/tip44.htm)
Click to expand...
 
cheers all who replied .

Heres how i did it in the end

The code works well by assigning a checkbox for every sheet in workbook
(upto 100)

Then processing the the ones checked .


Private Sub UserForm_Initialize()

Dim wsSheet As Worksheet
Dim chkbx As Control
Dim currentsheet As String
Dim Chkbxtop As Long
Dim Chkbxleft As Long
Dim FormWidth As Long
Dim FormHeight As Long
Dim nChkBx As Byte


nChkBx = 0
Chkbxtop = 15
Chkbxleft = 6
FormWidth = 100
FormHeight = 255
ButtonPos = 1
CommandButton1.Top = 185
CommandButton2.Top = 211

For Each wsSheet In Worksheets
If nChkBx = 10 Then
FormHeight = 425
CommandButton1.Top = 354
CommandButton2.Top = 381
ElseIf nChkBx = 20 Then
Chkbxleft = 100
Chkbxtop = 15
FormWidth = 200
ElseIf nChkBx = 40 Then
Chkbxleft = 200
Chkbxtop = 15
FormWidth = 300
ElseIf nChkBx = 60 Then
Chkbxleft = 300
Chkbxtop = 15
FormWidth = 400
ElseIf nChkBx = 80 Then
Chkbxleft = 400
Chkbxtop = 15
FormWidth = 500
ElseIf nChkBx > 100 Then
MsgBox "Error To many sheets"
Exit Sub
End If

Chkbxtop = Chkbxtop + 15
nChkBx = nChkBx + 1

Set chkbx = Select_Sheets.Controls.Add("Forms.checkbox.1",
"checkbox" & nChkBx, True)
With chkbx
.Caption = wsSheet.Name
.Accelerator = "N"
.Left = Chkbxleft
.Top = Chkbxtop
End With
Next wsSheet

Select_Sheets.Width = FormWidth
Select_Sheets.Height = FormHeight
CommandButton1.Left = FormWidth - 80
CommandButton2.Left = FormWidth - 80


End Sub


With this to process form
Private Sub CommandButton1_Click()
Dim wsSheet As Worksheet
Dim currentsheet As String
Dim chkbx As Control


currentsheet = ActiveSheet.Name

For Each chkbx In Me.Controls
If Left(chkbx.Name, 8) = "checkbox" Then
If chkbx.Value = True Then

Sheets(chkbx.Caption).Select
If PIntOrExt = "Int" Then
Run HF_Current(Pclientname, Pchargecode)
ElseIf PIntOrExt = "Ext" Then
Run EX_HF_Current(PCompany)
End If
End If
End If
Next

Sheets(currentsheet).Select
Unload Select_Sheets
End Sub
 
Back
Top