Question about a tricky Do...While loop

[IT_roofer]

I have a userform with a _bunch_ of checkboxes on several different pages of
a UserForm...

On the last page of the Userform are 24 multi-column comboboxes with
corresponding labels that are used for calculations...

Every time I check/uncheck a box somewhere in the userform, an item gets
added to a *single* combo on that last page (not all work yet - still adding
code to them). What I would like to do is have that single checked/unchecked
item be added/removed to all 24 combos on the last page..

It's kind of hard to explain, I guess. Below is the code I came up with to
add/remove items to/from *one* combo. I would like help with adding to this
code to include all combos 1-24. I'm thinking a for...next loop would do it,
but I'm terrible with loops. I usually crash machines when I try to program
them and I was extremely happy when I came up with the code that
adds/removes... so go easy on me! (ok, ok here's the code)

Public getCount, Counter, Check

Private Sub s4500_Click()
If s4500.Value = True Then
getCount = calcbox.ListCount
With calcbox
.AddItem "Tearoff Existing"
.List(getCount, 1) = rd_totalsqs.Text
End With
s4500a.Enabled = True
s4500a.Text = rd_totalsqs.Text
s4500b.Enabled = True
Else
Check = False
Counter = 0
getCount = calcbox.ListCount
Do
Do While Counter <> getCount
If calcbox.List(Counter, 0) = "Tearoff Existing" Then
calcbox.RemoveItem (Counter)
Check = True
Exit Do
Else
Counter = Counter + 1
End If
Loop
Loop Until Check = True
s4500a.Enabled = False
s4500a.Text = "0000"
s4500b.Enabled = False
End If
End Sub

 

[Incidental]

Hi

The code below should help you out or at least give you an idea of
where to start. To test add the code to a new module then on the
userform add a multipage, on the first page add a checkbox and on the
second page add as many comboboxes as you want.

'Add this code to a new module
Option Explicit
Dim CboBox() As New Class1
Dim Ctrl As Control
Dim i, X, Counter As Integer
Dim ChckVal
Sub AddToAllComboboxes()
ChckVal = UserForm1.MultiPage1.Pages(0).CheckBox1.Caption
i = 1
For Each Ctrl In UserForm1.MultiPage1.Pages(1).Controls
If TypeOf Ctrl Is MSForms.ComboBox Then
Set Ctrl =
UserForm1.MultiPage1.Pages(1).Controls("ComboBox" & i)
Ctrl.AddItem ChckVal
i = i + 1
End If
Next Ctrl
End Sub
Sub RemoveFromAllComboboxes()
i = 1
For Each Ctrl In UserForm1.MultiPage1.Pages(1).Controls
If TypeOf Ctrl Is MSForms.ComboBox Then
Set Ctrl =
UserForm1.MultiPage1.Pages(1).Controls("ComboBox" & i)
Counter = Ctrl.ListCount
X = 0
Do While X < Counter
Ctrl.ListIndex = (X)
If Ctrl.Value = ChckVal Then
Ctrl.RemoveItem Ctrl.ListIndex
End If
X = X + 1
Loop
i = i + 1
End If
Ctrl.Value = ""
Next Ctrl
End Sub

Then add this code to the click event of the checkbox

Private Sub CheckBox1_Click()
If UserForm1.MultiPage1.Pages(0).CheckBox1.Value = True Then
AddToAllComboboxes
Else
RemoveFromAllComboboxes
End If
End Sub

Hope this is of some help to you

S

PS isn't it hard to do IT on the Roof???

 

[IT_roofer]

Hey thanks for the code. I'll see what I can do with it. Some pieces I'll
have to change due to naming conventions, but I *think* I understand what the
code is doing. I'll let you know in a bit!

Oh yeah... and no, it's not hard to do it on the roof at all.
Laptop + broadband card = freedom!

Thanks again - J

 

[IT_roofer]

Well damn. The code looked good, however... it's halting at
"Set Ctrl = projectinfo.multiform1.pages(7).Controls("calcbox" & i)"
with the error "Can't find the object specified" - Can't quite figure out
why, but I'll kepp pluggin' away...

 

[Incidental]

Hey there

It is a multipage you're using???

Did you try just running a test run by making a test userform to see
how it works?? I am at home today and I wrote the code in work
yesterday, when I tried to run it I got a problem with the first line
in the module "Dim CboBox() As New Class1" though that was easily
sorted by adding a new class module to the project (must have had one
yesterday from the different variation I had tried to get this
running) after that the code runs fine for me though I admit it is
just a basic test scenario...

If you want to post a more descript version of what you are doing
(With names for controls and the like) I will try to take a look at it
and see what I can figure out.

So are you a real roofer then with shingles, buckets of tar, flashing
and all that fun stuff!!! Or is it more a take on "fiddler on the
roof", which now that I think about it when you think of internet
access and fiddling it conjures a very rude image lol.

S