Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about a tricky Do...While loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about a tricky Do...While loop
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??? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about a tricky Do...While loop
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 "Incidental" wrote: 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??? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about a tricky Do...While loop
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" wrote: 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??? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about a tricky Do...While loop
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tricky Macro Question | Excel Worksheet Functions | |||
Tricky Ranking Question | Excel Discussion (Misc queries) | |||
Tricky comparing question | Excel Worksheet Functions | |||
New guy with a tricky question | Excel Programming | |||
Tricky Question | Excel Worksheet Functions |