newbie vba questions & help with posible loop for multiple cell values to sheet

[Rasscal]

hi all,

quick question -
would vba userforms run quicker if

1. sheet dims was set at explicity rather than referenced multiple times in different subs?
2. are there are speed variences between protect/unprotect all sheets & protect/unprotect workbook at userform load

with a separate userform (code below & workbook attached)
I would like some help, i'm thinking this would have to be a loop but my previous experiences with loops have not been great, & with my newbie knowledge of vba im struggling to get my head around it.

i would like to enter a number in a txtbox (txtBox.value) & this value writes to the label sheet & print x amount of required labels

eg.
txtBox.value = 10

result would be

Column A & B
txtForename.Value
txtSurname.Value
cboType.Value
box 1 of 10
Column C & D
txtForename.Value
txtSurname.Value
cboType.Value
box 2 of 10
Column E & F
txtForename.Value
txtSurname.Value
cboType.Value
box 3 of 10

and so on.

could somebody be so kind to help me with this?
thanks

Sub Clear()


Dim ctl As Control
For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.Text = ""
Case "ListBox"
ctl.RowSource = ""
Case "ComboBox"
ctl.Value = ""
Case "ListBox"
ctl.Value = ""
Case "CheckBox"
ctl.Value = False
End Select
Next ctl


Me.txtDate.Value = Date


End Sub




Private Sub cboType_Change()


'make other fields visible
 txtInternal.Visible = cboType = "Internal"
 labelInternal.Visible = cboType = "Internal"
 labelInternal2.Visible = cboType = "Internal"


End Sub


Private Sub cmdClear_Click()


'clear controls
Clear


End Sub




Private Sub cmdPrint_Click()
Dim cBox As VbMsgBoxResult
Dim LabelSH As Worksheet
Set LabelSH = Sheet1


'Validation


If Me.txtForename.Value = "" Then
    MsgBox "Please Enter A First Name", vbCritical
    Exit Sub
    
End If


If Me.txtSurname.Value = "" Then
    MsgBox "Please Enter A Last Name", vbCritical
    Exit Sub


End If


If Me.cboType.Value = "" Then
    MsgBox "Please Select A Sample Type.", vbCritical
    Exit Sub
End If


    If Me.txtInternal.Visible = True And Len(Me.txtInternal.Value) <> 7 Then
        MsgBox "Please Enter A Valid Order Number", vbCritical
        Me.txtInternal.Value = Left(Me.txtInternal.Value, 7)
    Exit Sub
        
End If
    
If Me.txtBox.Value = "" Then
    MsgBox "Please Specify Number of Boxes", vbCritical
    Exit Sub


End If


If Me.txtBox.Value > 15 And Me.txtBox.Value <= 30 Then
cBox = MsgBox("You Are Sure You Want To Print" & " " & Me.txtBox.Value & " " & "Labels?", vbOKCancel + vbDefaultButton2 + vbExclamation)
End If
If cBox = vbCancel Then
Me.txtBox.Value = ""
Exit Sub
End If


If Me.txtBox.Value > 30 Then
    MsgBox "Please Enter A Valid Number of Boxes", vbCritical
    Me.txtBox.Value = ""
    Exit Sub
End If


''''''''''''''''''''''''
'add data to data sheet
''''''''''''''''''''''''












'clear old data from the label sheet
LabelSH.Cells.Clear


'add data to label sheet
LabelSH.Range("A1").Value = txtForename.Value
LabelSH.Range("B1").Value = txtSurname.Value
LabelSH.Range("A3").Value = cboType.Value
If cboType.Value = "Internal" Then
LabelSH.Range("B3").Value = "TT" & txtInternal.Value
End If
LabelSH.Range("A5").Value = Format(txtDate.Value, "Long Date")
LabelSH.Range("A7").Value = "Box 1 of" & " " & txtBox.Value


'adjust font size & position
FormatRange LabelSH.Range("A1:BH7"), 30
LabelSH.Range("A1").HorizontalAlignment = xlRight
LabelSH.Range("B1").HorizontalAlignment = xlLeft
LabelSH.Range("A3").HorizontalAlignment = xlRight
LabelSH.Range("B3").HorizontalAlignment = xlLeft
LabelSH.Range("A5:B5").Merge
LabelSH.Range("A5").HorizontalAlignment = xlCenter
LabelSH.Range("A7").HorizontalAlignment = xlRight


'print labels
LabelSH.Visible = True
LabelSH.PrintOut ActivePrinter:="ZDesigner ZD230-203dpi ZPL"
'LabelSH.Visible = False


''''''''''''''''''''''
'send email to contact
''''''''''''''''''''''














'clear the controls
Clear


'communicate with the user
MsgBox "Sample Printed Successfully", vbInformation


'return to data sheet
'Sheet3.Select


'close form
Unload Me




End Sub






Private Sub txtBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)


'only allow numeric characters
Debug.Print KeyAscii
If KeyAscii >= 48 And KeyAscii <= 57 Then
    Debug.Print "number"
Else
    Debug.Print "other"
    KeyAscii = 0
End If


End Sub


Private Sub txtForename_Change()


'changes the first letter to a capital
    txtForename.Value = _
    Application _
    .WorksheetFunction _
    .Proper(txtForename)


End Sub


Private Sub txtInternal_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)


'only allow numeric characters
Debug.Print KeyAscii
If KeyAscii >= 48 And KeyAscii <= 57 Then
    Debug.Print "number"
Else
    Debug.Print "other"
    KeyAscii = 0
End If


End Sub


Private Sub txtSurname_Change()


'changes the first letter to a capital
    txtSurname.Value = _
    Application _
    .WorksheetFunction _
    .Proper(txtSurname)


End Sub


Private Sub UserForm_Initialize()


Me.txtDate.Value = Date


End Sub

[paulked]

Like this?
Samples.xlsm

[Rasscal]

wow perfect, my only issue was becuase of the cell formats (merged cells) i was getting 30 prints with only 1 label written.

so i added -
If Not IsEmpty(bx) Then
to my .print fixed the issue.

thank you.

[paulked]

Glad to have helped