ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,699
- Office Version
- 2007
- Platform
- Windows
Hi,
I have the code shown below for a userform to worksheet.
The problem i have is the following.
I mainly use Royal Mail and the tracking format is like so RS1234567GB
Sometimes i use Hermes and the tracking is like 123456789123
The hermes transfers to the worksheet fine BUT is shown like 134+45+49=3 etc
Can something be done so if it doesnt start with a letter etc then import it as TEXT so i then see on my worksheet 123456789123
I have the code shown below for a userform to worksheet.
The problem i have is the following.
I mainly use Royal Mail and the tracking format is like so RS1234567GB
Sometimes i use Hermes and the tracking is like 123456789123
The hermes transfers to the worksheet fine BUT is shown like 134+45+49=3 etc
Can something be done so if it doesnt start with a letter etc then import it as TEXT so i then see on my worksheet 123456789123
Code:
Private Sub CommandButton1_Click()Cancel = 0
If TextBox2.Text = "" Then
Cancel = 1
MsgBox "Customer`s Name Not Entered", vbCritical, "POSTAGE TRANSFER SHEET"
TextBox2.SetFocus
ElseIf TextBox3.Text = "" Then
Cancel = 1
MsgBox "Item Description Not Entered", vbCritical, "POSTAGE TRANSFER SHEET"
TextBox3.SetFocus
ElseIf TextBox4.Text = "" Then
Cancel = 1
MsgBox "Tracking Number Not Entered", vbCritical, "POSTAGE TRANSFER SHEET"
TextBox4.SetFocus
ElseIf ComboBox1.Text = "" Then
Cancel = 1
MsgBox "Username Not Entered", vbCritical, "POSTAGE TRANSFER SHEET"
TextBox5.SetFocus
ElseIf OptionButton1.Value = False And OptionButton2.Value = False And OptionButton3.Value = False Then
Cancel = 1
MsgBox "You Must Select An Ebay Account", vbCritical, "POSTAGE TRANSFER SHEET"
ElseIf OptionButton4.Value = False And OptionButton5.Value = False And OptionButton6.Value = False Then
Cancel = 1
MsgBox "You Must Select An Origin", vbCritical, "POSTAGE TRANSFER SHEET"
End If
If Cancel = 1 Then
Exit Sub
End If
Dim i As Long
Dim x As Long
Dim ctrl As Control
Dim LastRow As Long
LastRow = ThisWorkbook.Worksheets("POSTAGE").Cells(Rows.Count, 1).End(xlUp).Row
With ThisWorkbook.Worksheets("POSTAGE")
.Cells(LastRow + 1, 1).Value = TextBox1.Text: TextBox1.Value = ""
.Cells(LastRow + 1, 2).Value = TextBox2.Text: TextBox2.Value = ""
.Cells(LastRow + 1, 3).Value = TextBox3.Text: TextBox3.Value = ""
.Cells(LastRow + 1, 5).Value = TextBox4.Text: TextBox4.Value = ""
.Cells(LastRow + 1, 9).Value = ComboBox1.Text: ComboBox1.Value = ""
.Cells(LastRow + 1, 4).Value = TextBox6.Text: TextBox6.Value = ""
If OptionButton1.Value = True Then .Cells(LastRow + 1, 8).Value = "DR": OptionButton1.Value = False
If OptionButton2.Value = True Then .Cells(LastRow + 1, 8).Value = "IVY": OptionButton2.Value = False
If OptionButton3.Value = True Then .Cells(LastRow + 1, 8).Value = "N/A": OptionButton3.Value = False
If OptionButton4.Value = True Then .Cells(LastRow + 1, 6).Value = "EBAY": OptionButton4.Value = False
If OptionButton5.Value = True Then .Cells(LastRow + 1, 6).Value = "WEB SITE": OptionButton5.Value = False
If OptionButton6.Value = True Then .Cells(LastRow + 1, 6).Value = "N/A": OptionButton6.Value = False
End With
TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
TextBox2.SetFocus
TextBox1.Value = Now
TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
End Sub
Private Sub CommandButton2_Click()
Unload PostageTransferSheet
End Sub
Private Sub DHL_Click()
ActiveWorkbook.FollowHyperlink Address:="http://www.dhl.co.uk/en/express/tracking.html", NewWindow:=True
End Sub
Private Sub HERMES_Click()
ActiveWorkbook.FollowHyperlink Address:="https://www.myhermes.co.uk/tracking-results.html", NewWindow:=True
End Sub
Private Sub LABEL_Click()
TrackingLabel.Show
End Sub
Private Sub ROYALMAIL_Click()
ActiveWorkbook.FollowHyperlink Address:="https://www.royalmail.com/track-your-item", NewWindow:=True
End Sub
Private Sub TextBox1_Change()
TextBox1 = UCase(TextBox1)
End Sub
Private Sub TextBox2_Change()
TextBox2 = UCase(TextBox2)
End Sub
Private Sub TextBox3_Change()
TextBox3 = UCase(TextBox3)
End Sub
Private Sub TextBox4_Change()
TextBox4 = UCase(TextBox4)
End Sub
Private Sub ComboBox1_Change()
ComboBox1 = UCase(ComboBox1)
End Sub
Private Sub TextBox6_Change()
TextBox6 = UCase(TextBox6)
End Sub
Private Sub CustomerSearchBox_Change()
'Modified 10/3/2018 5:51:42 AM EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = CustomerSearchBox.Value
Dim LastRow As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set SearchRange = Range("B2:B" & LastRow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & " Not Found": Exit Sub
SearchRange.Select
Unload Me
End Sub
Private Sub UserForm_Initialize()
'Modified 10/3/2018 5:51:42 AM EDT
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Sheets("POSTAGE").Cells(Rows.Count, "B").End(xlUp).Row
Dim Lastrowa As Long
Sheets("POSTAGE").Cells(8, 2).Resize(LastRow - 7).Copy Sheets("POSTAGE").Cells(1, 12)
Lastrowa = Sheets("POSTAGE").Cells(Rows.Count, "L").End(xlUp).Row
Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Sort key1:=Cells(1, 12).Resize(Lastrowa), order1:=xlAscending, Header:=xlNo
CustomerSearchBox.List = Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Value
Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Clear
Application.ScreenUpdating = True
'USERNAME COMBOBOX
TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
TextBox2.SetFocus
End Sub
Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim fndRng As Range, findString As String, i As Integer
If Me.TextBox2.Value = "" Then Exit Sub
findString = Me.TextBox2.Value
With Sheets("POSTAGE").Range("B:B")
Set fndRng = .Find(What:=findString, LookIn:=xlValues, lookat:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not fndRng Is Nothing Then
'what was entered already exists - alter the name until not found
For i = 2 To 20
findString = Me.TextBox2.Value & " " & i
Set fndRng = .Find(What:=findString, LookIn:=xlValues, lookat:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If fndRng Is Nothing Then Exit For
Next i
'message saying what name should be
'MsgBox "The name to use is " & findString
'enter that name into textbox 2
With Me.TextBox2
.Value = findString
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
'cancel moving out of text box
Cancel = True
End If
End With
End Sub