Trying to create a for.....next loop with checkbox's

dan2

Board Regular
Joined
Mar 26, 2002
Messages
60
Hi all

Here is the loop i have written.

Dim m As CheckBox
For t = 1 To 2
m = t
If usrfrmindsht(m).Value = True Then
imgcld.Visible = True
y = "pap" & t
Workbooks.Open _ Filename:="c:flexasol" & y, Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.PrintOut
ActiveWorkbook.Close
End If
Next t

excuse the spacing.

what i am trying to do is:

I have 33 checkboxes on a userform and i want it to check each box to see if it is checked and then print out the relevant sheets to the boxes that are checked.

It chooses the right sheet but i cant get it to do the checkbox part.

Any ideas please i banging my head against a brick wall over this one.

Thanks

Dan
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
One way would be to connect each checkbox to a cell in a worksheet, then check the cells.
cells z3 and z4 read true or false from checkbox (chkbox property - controlsource, or you could loop thru to chech each Checkbox's value property. Err is msgbox if none selected.


Sub prnt()
' prnt Macro
' Macro recorded 10/25/2001 by Charles Weber
Err = Range("Z9")
If Err = 0 Then
np.Show
Exit Sub
End If
If Range("z3") = True Then
Application.Sheets("Appendix B").PrintOut
End If
If Range("z4") = True Then
Application.Sheets("Appendix C").PrintOut
End If
End Sub
 

dan2

Board Regular
Joined
Mar 26, 2002
Messages
60
Thanks

But i there are 33 checkboxes at the moment and more will be added later so i am trying to find a way to use a for....next loop to try and reduce the amount of code for the form produced from using if statements.
 

zacemmel

Well-known Member
Joined
Apr 29, 2002
Messages
636

ADVERTISEMENT

Is pap the name of your checkboxes? I have some code that will do exactly what you want, if that is the case :)
 

dan2

Board Regular
Joined
Mar 26, 2002
Messages
60
at the moment the checkboxes are called checkbox1,checkbox2 .... etc and the sheets to print out are pap1, pap2 ..... etc

if checkbox1 is checked then pap1 is printed etc

the naming of the checkboxes and the sheets can be changed if needed.
 

zacemmel

Well-known Member
Joined
Apr 29, 2002
Messages
636

ADVERTISEMENT

Try this:
set myObj = yourform.controls
for i = 0 to (myObj.count-1)
if left(myObj.item(i).name,8)= "Checkbox" and myobj.item(i).value = true then
'your code here
end if
next i

note: i just wrote that freehand, so there might be some minor syntax errors ;p
This message was edited by zacemmel on 2002-10-25 17:25
 

dan2

Board Regular
Joined
Mar 26, 2002
Messages
60
the following error comes up when i try the code above

Wrong number of arguments or invalid property assignment.

and it highlights the for line
 

Forum statistics

Threads
1,144,312
Messages
5,723,654
Members
422,508
Latest member
Lordkit1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top