to Generate unqiue code on userform

[SHWAZ]

Dear all,

I am a vba noob, just started building a userform for a month, able to send all the data to excel sheet, able to perform search function and recall back to another userform and etc

I need your help on the below scenarios, I was trying to google the answer for a few hours and couldn't find any solution close to my requirements.
I tried to user formula to get what I needed once the data was input by users and saved to the worksheet.
=IF(B2<>"",IF(B2="Complaint","C","E")&IF(A2<>"",MID(YEAR(J2),3,2)&TEXT(J2,"mm")&ExtractCap(G2)&TEXT(K2,"hhmmss"),""),"")
where B2 =
G2 = name
K2 = start time
J2 = time

However, I want to show same code on userform before users save data to excel sheet. (i.e. create a unique code base on what users input into the userform before saved to worksheet)
I created a "Start Call" button to generate current time and current date on two different textbox.
A combo box for users to pick "handled by" (i.e. name of handler), lets say the names are :
Pxxx Lxxx
Cxxx Mxxx
Cxxx Cxxx
Nxxx Cxxx


A combo box for users to pick enquiry type,
Complaint
Product Enquiry
Endorsement Enquiry
Claims Enquiry
Sales Enquiry
General Enquiry

The unique code should be generated when above fields are filled (or not empty you could call that)
e.g. E1710PL003651 and C1710NC003823

1st letter, C = Complaint, otherwise E = other enquires
17 = YY, extract year from date
10 = MM in digital, extract month from date
6th - 7th letter, the initial of handlers, i.e. PL or NC
the digital behind is just the current time in hhmmss format

If one of these fields are empty, label or text box could show "Not enough info to generate code"
All I could think of its to use if functions for each fields and generate a value
Then a label or textbox to show a combination of different values.
But I couldn't get the data to show on the label when the "handle by" is not empty and not sure how to set up values for each fields.

It would be great if any of you experts could advise or give me some ideas how to tackle such issue, thankyou all in advance!

Regards
Tom

 

[SHWAZ]

push for help, thanks!!

 

[steve the fish]

Hi. This code may help you along:

Private Sub ComboBox1_Change()

CBChanges

End Sub

Private Sub ComboBox2_Change()

CBChanges

End Sub

Sub CBChanges()

Dim typecall As String, initial As String, str As String

TextBox1.Value = ""
If Len(ComboBox1.Value) > 0 And Len(ComboBox2.Value) > 0 Then
    typecall = IIf(ComboBox2.Value = "Complaint", "C", "E")
    initial = Left(ComboBox1.Value, 1) & Mid(ComboBox1.Value, InStr(ComboBox1.Value, " ") + 1, 1)
    str = typecall & Format(Date, "YYMM") & initial & Format(Time, "HHMMSS")
End If
If Len(str) > 0 Then TextBox1.Value = str

End Sub

 

[SHWAZ]

Hi. This code may help you along:

Private Sub ComboBox1_Change()

CBChanges

End Sub

Private Sub ComboBox2_Change()

CBChanges

End Sub

Sub CBChanges()

Dim typecall As String, initial As String, str As String

TextBox1.Value = ""
If Len(ComboBox1.Value) > 0 And Len(ComboBox2.Value) > 0 Then
    typecall = IIf(ComboBox2.Value = "Complaint", "C", "E")
    initial = Left(ComboBox1.Value, 1) & Mid(ComboBox1.Value, InStr(ComboBox1.Value, " ") + 1, 1)
    str = typecall & Format(Date, "YYMM") & initial & Format(Time, "HHMMSS")
End If
If Len(str) > 0 Then TextBox1.Value = str

End Sub

Thanks for your advise steve, will look at your code now.

I was able to perform similar stuff with much longer code..... but I need to click on the label in order to generate the number
If there any way to generate the code automatically once users input specific fields? Thanks a lot bro!



Private Sub ECCODE_Click()
Dim ECvar1 As String
Dim ECvar2 As String
Dim ECvar3 As String
Dim ECvar4 As String

If Me.Reg7.Value = "" Or Me.Reg10.Value = "" Or Me.Reg11.Value = "" Or Me.Reg2.Value = "" Then
ECCODE.Caption = "Not enough info to generate"

Else
'Handler
If Me.Reg7.Value = "PXXX LXX" Then
ECvar1 = "PL"
ElseIf Me.Reg7.Value = "NXXX CXX" Then
ECvar1 = "NC"
ElseIf Me.Reg7.Value = "CXXX MXX" Then
ECvar1 = "CM"
ElseIf Me.Reg7.Value = "CXXX CXX" Then
ECvar1 = "CC"
ElseIf Me.Reg7.Value = "" Then
ECvar1 = "Not enough info to generate"
End If

'E/C type
If Me.Reg2.Value = "Complaint" Then
ECvar2 = "C"
ElseIf Me.Reg2.Value = "" Then
ECvar2 = ""
Else
ECvar2 = "E"
End If

'Year and month
If Me.Reg10.Value > 1 Then
ECvar3 = Format(Me.Reg10.Value, "YYMM")
Else
ECvar3 = ""
End If

'time
If Me.Reg11.Value > 1 Then
ECvar4 = Format(Me.Reg11.Value, "hhmmss")
Else
ECvar4 = ""
End If

ECCODE.Caption = ECvar2 & ECvar3 & ECvar1 & ECvar4
Me.Repaint
End If

End Sub

 

[steve the fish]

The code will be triggered each time one of the comboboxes is changed. It will fill in the textbox when both comboboxes are full. You need to change the code to reflect the names of your comboboxes/ textbox.

 

[SHWAZ]

The code will be triggered each time one of the comboboxes is changed. It will fill in the textbox when both comboboxes are full. You need to change the code to reflect the names of your comboboxes/ textbox.

Your code works great and very suitable for me! I am inspired and understand a little bit more how to set up the code now!!
thanks bro! you are awesome!

 

[SHWAZ]

HI Steve,

Are you still around? I changed the label to a text box for users to amend.

Could you please advise how to amend your coding for below situation:
What if users want to manual amend the code? Currently your code auto default the values and I couldn't make any amendment to the text box

Big thankyou in advance!! Tom