Checking a userForm that has not been initialized

[Arne Hegefors]

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!!

 

[Tom Ogilvy]

in a general module

Public NumCharts


in Userform1

Private Sub Userform_Initialize()
NumCharts = 10
End Sub

in Userform 2
code that counts the checkboxes and sets NumCharts.

Somewhere else - code that uses numcharts to create the number of charts.

 

[Arne Hegefors]

thanks Tom!! My problem is that the default value for NumCheckBoxes cannot be
a random number but I need the actual number of CheckBoxes. Is that possible
at all? Would really appreciate help! Thanks again for your assistance!

"Tom Ogilvy" skrev:

 

[Tom Ogilvy]

It would be hard to imagine an instance where you would not know how many
checkboxes are on userform2, but if that is the case:

Sub CodeToBuildcharts()
if Numcheckboxes = 0 then
load Userform2
cnt = 0
for each ctl in Userform2.Controls
if typeof ctl is MSforms.checkbox then
cnt = cnt + 1
end if
next
Numcheckboxes = cnt
end if
Unload Userform2
' now work with numcheckboxes
End sub

 

[Arne Hegefors]

great thanks Tom! I know that it seems strange but I need to let other people
work around with the program as much as possible and they are not skilled in
programming (neither am I for that matter..). Anyway thanks alot Tom!!!

"Tom Ogilvy" skrev: