PDA

View Full Version : newbie vba questions & help with posible loop for multiple cell values to sheet



Rasscal
11-06-2019, 05:08 AM
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





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
11-06-2019, 05:46 AM
Like this?
25378

Rasscal
11-06-2019, 06:08 AM
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
11-06-2019, 06:12 AM
Glad to have helped :thumb