Hi,

I need a macro code to use in a userform so that when it is clicked it will
generate a unique reference code. Also, if the information entered in the
userform already matches information entered on the existing data sheet then
it will display a text box which contains "INFORMATION ALREADY IN DATA SHEET"

Thanks for the help everyone!

Hi.
First, create a name :
ActiveWorkbook.Names.Add "RefCode", RefersTo:=1, Visible:=False
(Creating it with VBA allows you to hide the name).
Next, increment the value :
Var = [RefCode] + 1
and assign the new value to the name :
ActiveWorkbook.Names.Add "RefCode", RefersTo:=Var, Visible:=False
So, when you save the file, you save the name and its value.
To check if a textbox content matches a value in column A :
If IsNumeric(Application.Match(Me.Textbox1, [A:A], 0)) Then
MsgBox "INFORMATION ALREADY IN DATA SHEET"
End If
HTH
Daniel

Hi,

I need a macro code to use in a userform so that when it is clicked it will
generate a unique reference code. Also, if the information entered in the
userform already matches information entered on the existing data sheet then
it will display a text box which contains "INFORMATION ALREADY IN DATA SHEET"

Thanks for the help everyone!

Hi Daniel,

Thank you for that, im a novice here, kindly please help me insert the
following code. im using this code:


Private Sub cmdAdd_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("SIData")

'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for user
If Trim(Me.cbouser.Value) = "" Then
Me.cbouser.SetFocus
MsgBox "Please enter user"
Exit Sub
End If

'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.cbouser.Value
.Cells(lRow, 2).Value = Me.cbotenor.Value
.Cells(lRow, 3).Value = Me.txtDate.Value
.Cells(lRow, 4).Value = Me.txtcn.Value
.Cells(lRow, 6).Value = Me.txtTA.Value
.Cells(lRow, 7).Value = Me.txtamt.Value
.Cells(lRow, 8).Value = Me.txttf.Value
.Cells(lRow, 9).Value = Me.cboRM.Value
.Cells(lRow, 10).Value = Me.txtref.Value
.Cells(lRow, 11).Value = Me.txtrem.Value
End With

'clear the data
Me.cbouser.Value = ""
Me.cbotenor.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtcn.Value = ""
Me.txtTA.Value = ""
Me.txtamt.Value = ""
Me.txttf.Value = ""
Me.cboRM.Value = ""
Me.txtref.Value = ""
Me.txtrem.Value = ""
Me.cbouser.SetFocus

End Sub

THANKS!

"Daniel.C" wrote:

Hi.
First, create a name :
ActiveWorkbook.Names.Add "RefCode", RefersTo:=1, Visible:=False
(Creating it with VBA allows you to hide the name).
Next, increment the value :
Var = [RefCode] + 1
and assign the new value to the name :
ActiveWorkbook.Names.Add "RefCode", RefersTo:=Var, Visible:=False
So, when you save the file, you save the name and its value.
To check if a textbox content matches a value in column A :
If IsNumeric(Application.Match(Me.Textbox1, [A:A], 0)) Then
MsgBox "INFORMATION ALREADY IN DATA SHEET"
End If
HTH
Daniel

Hi,

I need a macro code to use in a userform so that when it is clicked it will
generate a unique reference code. Also, if the information entered in the
userform already matches information entered on the existing data sheet then
it will display a text box which contains "INFORMATION ALREADY IN DATA SHEET"

Thanks for the help everyone!

Hi Rachel.
Well, I am using a french version of Excel, so sometimes I will just
guess what the equivalent terms may be.
1. go to the VB Editor (where you copy the macro)
2. if you have none, insert a general module and paste the following
macro. you'll have to use it only once, to create the name :

Sub CreateName()
ActiveWorkbook.Names.Add "RefCode", RefersTo:=0, Visible:=False
End Sub

In your macro, I added the reference code in column L :

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("SIData")

'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for user
If Trim(Me.cbouser.Value) = "" Then
Me.cbouser.SetFocus
MsgBox "Please enter user"
Exit Sub
End If

'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.cbouser.Value
.Cells(lRow, 2).Value = Me.cbotenor.Value
.Cells(lRow, 3).Value = Me.txtDate.Value
.Cells(lRow, 4).Value = Me.txtcn.Value
.Cells(lRow, 6).Value = Me.txtTA.Value
.Cells(lRow, 7).Value = Me.txtamt.Value
.Cells(lRow, 8).Value = Me.txttf.Value
.Cells(lRow, 9).Value = Me.cboRM.Value
.Cells(lRow, 10).Value = Me.txtref.Value
.Cells(lRow, 11).Value = Me.txtrem.Value
'*** Insertion of the reference code
Var = [RefCode] + 1
ActiveWorkbook.Names.Add "RefCode", RefersTo:=Var, Visible:=False
.Cells(lRow, 12) = [RefCode]
'or, if your reference mix string(s) and number :
.Cells(lRow, 12) = "ID" & [RefCode]
End With

'clear the data
Me.cbouser.Value = ""
Me.cbotenor.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtcn.Value = ""
Me.txtTA.Value = ""
Me.txtamt.Value = ""
Me.txttf.Value = ""
Me.cboRM.Value = ""
Me.txtref.Value = ""
Me.txtrem.Value = ""
Me.cbouser.SetFocus

End Sub

Regards.
Daniel

Hi Daniel,

Thank you for that, im a novice here, kindly please help me insert the
following code. im using this code:


Private Sub cmdAdd_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("SIData")

'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for user
If Trim(Me.cbouser.Value) = "" Then
Me.cbouser.SetFocus
MsgBox "Please enter user"
Exit Sub
End If

'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.cbouser.Value
.Cells(lRow, 2).Value = Me.cbotenor.Value
.Cells(lRow, 3).Value = Me.txtDate.Value
.Cells(lRow, 4).Value = Me.txtcn.Value
.Cells(lRow, 6).Value = Me.txtTA.Value
.Cells(lRow, 7).Value = Me.txtamt.Value
.Cells(lRow, 8).Value = Me.txttf.Value
.Cells(lRow, 9).Value = Me.cboRM.Value
.Cells(lRow, 10).Value = Me.txtref.Value
.Cells(lRow, 11).Value = Me.txtrem.Value
End With

'clear the data
Me.cbouser.Value = ""
Me.cbotenor.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtcn.Value = ""
Me.txtTA.Value = ""
Me.txtamt.Value = ""
Me.txttf.Value = ""
Me.cboRM.Value = ""
Me.txtref.Value = ""
Me.txtrem.Value = ""
Me.cbouser.SetFocus

End Sub

THANKS!

"Daniel.C" wrote:

Hi.
First, create a name :
ActiveWorkbook.Names.Add "RefCode", RefersTo:=1, Visible:=False
(Creating it with VBA allows you to hide the name).
Next, increment the value :
Var = [RefCode] + 1
and assign the new value to the name :
ActiveWorkbook.Names.Add "RefCode", RefersTo:=Var, Visible:=False
So, when you save the file, you save the name and its value.
To check if a textbox content matches a value in column A :
If IsNumeric(Application.Match(Me.Textbox1, [A:A], 0)) Then
MsgBox "INFORMATION ALREADY IN DATA SHEET"
End If
HTH
Daniel

Hi,

I need a macro code to use in a userform so that when it is clicked it will
generate a unique reference code. Also, if the information entered in the
userform already matches information entered on the existing data sheet
then it will display a text box which contains "INFORMATION ALREADY IN
DATA SHEET"

Thanks for the help everyone!