Userform.printform problem

[Emoncada]

I have this code
Private Sub CommandButton1_Click()
Dim i As Long, j As Long

Do
UserForm1.PrintForm
i = i + 1
Loop Until i = Me.TextBox1.Value

End Sub



This works fine the problem is i want to print out 2 sheets for each value. So if they enter 1 i want to print outs, but after the first print out i want to hide some objects that are visible and then make some others visible, Then print out the next printout. How can I make that happen.

[mikerickson]

i=0
Do
If i = 1 Then
Rem hide stuff
End if

UserForm1.PrintForm
i = i + 1
Loop Until i = Me.TextBox1.Value

[Emoncada]

hey mikerickson thanks for the quick reply but will this only work if the value is 1?

I basically did this as a work around instead of having multiple forms I created duplicate txtboxes, & cmbboxes. Those duplicate ones are not visible until a cmbbutton is clicked which hides the first set of txtboxes & cmbboxes and make the hidden ones visible for data entry. Now what I want is something like this

Option Explicit
Option Compare Text
Private Sub CommandButton1_Click()
Dim i As Long, j As Long


UserForm1.CmdClear.Visible = False
UserForm1.CmdDC7600.Visible = False
UserForm1.CmdDC7700.Visible = False
UserForm1.CmdNC8230.Visible = False
UserForm1.Cmd17IN.Visible = False
UserForm1.CmdNC8430.Visible = False
UserForm1.CmdPrintSave.Visible = False
UserForm1.ImagePrint.Visible = False
UserForm1.LblPacking.Visible = True
UserForm1.LblSlip.Visible = True
UserForm1.CmbBoxLocation.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxClientName.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxProject.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc1.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc2.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc3.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc4.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc5.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc6.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc7.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc8.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc9.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc10.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc11.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc12.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc13.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc14.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc15.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc16.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc17.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc18.ShowDropButtonWhen = fmShowDropButtonWhenNever

Do
UserForm1.PrintForm
i = i + 1
Loop Until i = Me.TextBox1.Value


UserForm1.TxtQua1.Visible = False
UserForm1.TxtQua2.Visible = False
UserForm1.TxtQua3.Visible = False
UserForm1.TxtQua4.Visible = False
UserForm1.TxtQua5.Visible = False
UserForm1.TxtQua6.Visible = False
UserForm1.TxtQua7.Visible = False
UserForm1.TxtQua8.Visible = False
UserForm1.TxtQua9.Visible = False
UserForm1.TxtQua10.Visible = False
UserForm1.TxtQua11.Visible = False
UserForm1.TxtQua12.Visible = False
UserForm1.TxtQua13.Visible = False
UserForm1.TxtQua14.Visible = False
UserForm1.TxtQua15.Visible = False
UserForm1.TxtQua16.Visible = False
UserForm1.TxtQua17.Visible = False
UserForm1.TxtQua18.Visible = False

'----------Show Sheet 2 Quantity------------------------

UserForm1.TxtQua19.Visible = True
UserForm1.TxtQua20.Visible = True
UserForm1.TxtQua21.Visible = True
UserForm1.TxtQua22.Visible = True
UserForm1.TxtQua23.Visible = True
UserForm1.TxtQua24.Visible = True
UserForm1.TxtQua25.Visible = True
UserForm1.TxtQua26.Visible = True
UserForm1.TxtQua27.Visible = True
UserForm1.TxtQua28.Visible = True
UserForm1.TxtQua29.Visible = True
UserForm1.TxtQua30.Visible = True
UserForm1.TxtQua31.Visible = True
UserForm1.TxtQua32.Visible = True
UserForm1.TxtQua33.Visible = True
UserForm1.TxtQua34.Visible = True
UserForm1.TxtQua35.Visible = True
UserForm1.TxtQua36.Visible = True

'----------Hide Sheet 1 Serial Numbers------------------------

UserForm1.TxtSN1.Visible = False
UserForm1.TxtSN2.Visible = False
UserForm1.TxtSN3.Visible = False
UserForm1.TxtSN4.Visible = False
UserForm1.TxtSN5.Visible = False
UserForm1.TxtSN6.Visible = False
UserForm1.TxtSN7.Visible = False
UserForm1.TxtSN8.Visible = False
UserForm1.TxtSN9.Visible = False
UserForm1.TxtSN10.Visible = False
UserForm1.TxtSN11.Visible = False
UserForm1.TxtSN12.Visible = False
UserForm1.TxtSN13.Visible = False
UserForm1.TxtSN14.Visible = False
UserForm1.TxtSN15.Visible = False
UserForm1.TxtSN16.Visible = False
UserForm1.TxtSN17.Visible = False
UserForm1.TxtSN18.Visible = False

'----------Show Sheet 2 Serial Numbers------------------------

UserForm1.TxtSn19.Visible = True
UserForm1.TxtSn20.Visible = True
UserForm1.TxtSn21.Visible = True
UserForm1.TxtSn22.Visible = True
UserForm1.TxtSn23.Visible = True
UserForm1.TxtSn24.Visible = True
UserForm1.TxtSn25.Visible = True
UserForm1.TxtSn26.Visible = True
UserForm1.TxtSn27.Visible = True
UserForm1.TxtSn28.Visible = True
UserForm1.TxtSn29.Visible = True
UserForm1.TxtSn30.Visible = True
UserForm1.TxtSn31.Visible = True
UserForm1.TxtSn32.Visible = True
UserForm1.TxtSn33.Visible = True
UserForm1.TxtSn34.Visible = True
UserForm1.TxtSn35.Visible = True
UserForm1.TxtSn36.Visible = True

'----------Hide Sheet 1 Description------------------------

UserForm1.CmbBoxDesc1.Visible = False
UserForm1.CmbBoxDesc2.Visible = False
UserForm1.CmbBoxDesc3.Visible = False
UserForm1.CmbBoxDesc4.Visible = False
UserForm1.CmbBoxDesc5.Visible = False
UserForm1.CmbBoxDesc6.Visible = False
UserForm1.CmbBoxDesc7.Visible = False
UserForm1.CmbBoxDesc8.Visible = False
UserForm1.CmbBoxDesc9.Visible = False
UserForm1.CmbBoxDesc10.Visible = False
UserForm1.CmbBoxDesc11.Visible = False
UserForm1.CmbBoxDesc12.Visible = False
UserForm1.CmbBoxDesc13.Visible = False
UserForm1.CmbBoxDesc14.Visible = False
UserForm1.CmbBoxDesc15.Visible = False
UserForm1.CmbBoxDesc16.Visible = False
UserForm1.CmbBoxDesc17.Visible = False
UserForm1.CmbBoxDesc18.Visible = False

'----------Show Sheet 2 Description------------------------

UserForm1.CmbBoxDesc19.Visible = True
UserForm1.CmbBoxDesc20.Visible = True
UserForm1.CmbBoxDesc21.Visible = True
UserForm1.CmbBoxDesc22.Visible = True
UserForm1.CmbBoxDesc23.Visible = True
UserForm1.CmbBoxDesc24.Visible = True
UserForm1.CmbBoxDesc25.Visible = True
UserForm1.CmbBoxDesc26.Visible = True
UserForm1.CmbBoxDesc27.Visible = True
UserForm1.CmbBoxDesc28.Visible = True
UserForm1.CmbBoxDesc29.Visible = True
UserForm1.CmbBoxDesc30.Visible = True
UserForm1.CmbBoxDesc31.Visible = True
UserForm1.CmbBoxDesc32.Visible = True
UserForm1.CmbBoxDesc33.Visible = True
UserForm1.CmbBoxDesc34.Visible = True
UserForm1.CmbBoxDesc35.Visible = True
UserForm1.CmbBoxDesc36.Visible = True

'----------Hide Sheet 1 Tracking------------------------

UserForm1.TxtTrack1.Visible = False
UserForm1.TxtTrack2.Visible = False
UserForm1.TxtTrack3.Visible = False
UserForm1.TxtTrack4.Visible = False
UserForm1.TxtTrack5.Visible = False
UserForm1.TxtTrack6.Visible = False
UserForm1.TxtTrack7.Visible = False
UserForm1.TxtTrack8.Visible = False
UserForm1.TxtTrack9.Visible = False
UserForm1.TxtTrack10.Visible = False
UserForm1.TxtTrack11.Visible = False
UserForm1.TxtTrack12.Visible = False
UserForm1.TxtTrack13.Visible = False
UserForm1.TxtTrack14.Visible = False
UserForm1.TxtTrack15.Visible = False
UserForm1.TxtTrack16.Visible = False
UserForm1.TxtTrack17.Visible = False
UserForm1.TxtTrack18.Visible = False

'----------Show Sheet 2 Tracking------------------------

UserForm1.TxtTrack19.Visible = True
UserForm1.TxtTrack20.Visible = True
UserForm1.TxtTrack21.Visible = True
UserForm1.TxtTrack22.Visible = True
UserForm1.TxtTrack23.Visible = True
UserForm1.TxtTrack24.Visible = True
UserForm1.TxtTrack25.Visible = True
UserForm1.TxtTrack26.Visible = True
UserForm1.TxtTrack27.Visible = True
UserForm1.TxtTrack28.Visible = True
UserForm1.TxtTrack29.Visible = True
UserForm1.TxtTrack30.Visible = True
UserForm1.TxtTrack31.Visible = True
UserForm1.TxtTrack32.Visible = True
UserForm1.TxtTrack33.Visible = True
UserForm1.TxtTrack34.Visible = True
UserForm1.TxtTrack35.Visible = True
UserForm1.TxtTrack36.Visible = True

UserForm1.CmbBoxDesc19.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc20.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc21.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc22.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc23.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc24.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc25.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc26.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc27.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc28.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc29.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc30.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc31.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc32.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc33.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc34.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc35.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc36.ShowDropButtonWhen = fmShowDropButtonWhenNever
Do
UserForm1.PrintForm
i = i + 1
Loop Until i = Me.TextBox1.Value

Unload FrmPrint

But of course this is not working. You have any ideas?

[Bob Phillips]

It is not absolutely clear what you want.

On what conditions do you show/hide those textboxes? Is it driven by the variable i or something else?

[Emoncada]

Well i am going to add a if statement.
If Userform1.CmbBoxDesc19.Value <> " Then


So if this does not = "" Then I want the userform to print out what ever i equals, BUT let says i = 1 Then have userform1 print page one then hide certain values and make others visible then print that userform1 again.

And same procedure no matter what i equals. If i = 3 then i want that procedure done 3 times.

[Emoncada]

hope that explains my goal

[Bob Phillips]

Not for me sorry.

Why do you want it to print page and then print it again if i =1, but print page 3 times if i = 3, that is not consistent to me.

And where does sheet 1, sheet 2, as perv your code come into play?

[mikerickson]

I contemplated putting a Select Case there rather than the block If.
If you want different things to happen on each loop of the Do,

Do

Select Case i
Case 1
Rem code
Case 2
Rem code
Case Else
Rem code
End Select

Rem Print

Loop

would be one way to go.

[Emoncada]

Sorry xld for the confusion.
Basically this is what i got.
TxtTrack1 -18
TxtSn1 - 18
CmbBoxDesc1 - 18
TxtQty1 - 18

These values would be visible on startup.

Now I also have values that are right behind these values just with status as visible = false.
Those values are
TxtTrack19 -36
TxtSn19 - 36
CmbBoxDesc19 - 36
TxtQty19 - 36

These values appear when a cmdbutton is clicked which hides the first set.

Then when saved it sends all values to spreadsheet and I would like for it to print out Userform1 first with the first set of values then another userform1 printout with the second set of values.

[Emoncada]

This is my work around instead of using two different forms which just wasn't doing what I wanted to do.

[Norie]

Emoncada

Why are you printing the userform in the first place?

Couldn't you transfer the data to a worksheet(s) and print that?

[Emoncada]

The userform is a Packing slip that needs to be printed out to be sent out with the package.

[Norie]

And it wouldn't be possible to reproduce this packing slip on a worksheet?

By the way the code you posted could be severely condensed.

For example this:

UserForm1.CmbBoxDesc1.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc2.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc3.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc4.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc5.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc6.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc7.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc8.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc9.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc10.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc11.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc12.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc13.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc14.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc15.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc16.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc17.ShowDropButtonWhen = fmShowDropButtonWhenNever
UserForm1.CmbBoxDesc18.ShowDropButtonWhen = fmShowDropButtonWhenNever

Can be reduced to this:

For X = 1 To 18
UserForm1.Controls("CmbBoxDesc" & X).ShowDropButtonWhen = fmShowDropButtonWhenNever
Next X

[Emoncada]

I originally had it on a worksheet but I am trying it now with the userforms and it's working great except for this problem. Thanks for the revised code I appreciate it.

[Emoncada]

Any Ideas?

[lucas]

Hi Em,
I'm with Norie. Put the data in a worksheet. You can format it to look the way you want it to and no problem getting it all on a sheet. Never understood printing a form....

[Norie]

Emoncada

I'm not suggesting you abandon the userform(s).

What I'm suggesting is that you transfer the data to a worksheet(s) and print that/them.

You could even use some sort of template worksheet.

I also wonder why you have so many controls, 195ish I think, on 1 userform.:dunno