1 Attachment(s)
newbie vba questions & help with posible loop for multiple cell values to sheet
hi all,
quick question -
would vba userforms run quicker if
- sheet dims was set at explicity rather than referenced multiple times in different subs?
- 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
Code:
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
