Add data through user form

Hello Again,

I am trying to add data from user form but i have issue while adding serial number in column A. VBA Macros showing error "Run-time error '13'. When i debug it is showing error on highlighted in red color on below code. Can anyone help me please.

Showing Error In Line:

  Cells(lNextRow, "A").Value = Cells(lNextRow - 1, "A").Value + 1

Full Code:

Private Sub CommandButton1_Click()

    Dim mystr, mytime
    Dim diff As Double
    Dim LastRow As Long
    Dim iRow As Long
    Dim rng As Range
    Dim ws As Worksheet
    Dim lNextRow As Long
    
'Worksheets("Master Sheet").Unprotect "***"
           
    LastRow = Range("A65536").End(xlUp).Row + 1
    mystr = Format("mm/dd/yyyy")
    mytime = Format("h:mm")
    mytime1 = TimeValue(TextBox4.Value)
    mytime2 = TimeValue(TextBox6.Value)
    
Worksheets("Master Sheet").Select
With Worksheets("Master Sheet")

    Set ws = Worksheets("Master Sheet")
   
   'find  first empty row in database
   iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
   
   'get all the database entires
    Set rng = ws.Range("B2", ws.Cells(iRow, "B"))
   
   'Check if part exits in database
    If Not rng.find(TextBox1, , xlValues, xlWhole, xlByRows, xlPrevious, False) Is Nothing Then
      response = MsgBox("Visa No. '" & TextBox1 & " 'already exits in database" _
      + vbCrLf + " " + vbCrLf + "  - Lets Make Life Easier...", _
      vbOKOnly + vbInformation, "Approval Entery (Date Format Issue)")
      Exit Sub
    End If
    
lNextRow = Cells(Rows.Count, "a").End(xlUp).Row + 1
Cells(lNextRow, "A").Value = Cells(lNextRow - 1, "A").Value + 1
    
    If TextBox1.Text <> "" Then
    .Range("B" & LastRow).Value = TextBox1.Text
    Else
    response = MsgBox("Cannot save data without VISA No." _
                    + vbCrLf + " " + vbCrLf + "  - Lets Make Life Easier...", _
                           vbOKOnly + vbInformation, "Approval Entery (Date Format Issue)")
    TextBox1.SetFocus
    Exit Sub
    End If
        
    If CheckBox1.Value = True Then
    .Range("C" & LastRow).Value = TextBox10.Text
    End If
    If CheckBox2.Value = True Then
    .Range("C" & LastRow).Value = TextBox10.Text
    End If
    If CheckBox3.Value = True Then
    .Range("C" & LastRow).Value = TextBox10.Text
    End If
    If CheckBox4.Value = True Then
    .Range("C" & LastRow).Value = TextBox10.Text
    End If
    If CheckBox5.Value = True Then
    .Range("C" & LastRow).Value = TextBox10.Text
    End If
    If CheckBox6.Value = True Then
    .Range("C" & LastRow).Value = TextBox10.Text
    End If
  
    .Range("D" & LastRow).Value = ComboBox1.Text
    .Range("E" & LastRow).Value = TextBox2.Text
    .Range("F" & LastRow).Value = Format(TextBox3.Text, mystr)
    .Range("G" & LastRow).Value = Format(TextBox4.Text, mytime)
    .Range("H" & LastRow).Value = Format(TextBox5.Text, mystr)
    .Range("I" & LastRow).Value = Format(TextBox6.Text, mytime)
    .Range("L" & LastRow).Value = TextBox7.Text
    .Range("M" & LastRow).Value = ComboBox2.Text
    
    dif = CDate(TextBox5.Value) - CDate(TextBox3.Value)
    TextBox8.Text = dif
    .Range("J" & LastRow).Value = TextBox8.Text
    
    mytime3 = mytime2 - mytime1
    TextBox9.Value = Format(mytime3, "hh:mm")
    .Range("K" & LastRow).Value = Format(TextBox9.Text, mytime)
    
   response = MsgBox("Record has been entered into sheet " & "'" & UCase(ActiveSheet.Name) & "'" & " successfully" _
                    + vbCrLf + " " + vbCrLf + "  - Lets Make Life Easier...", _
                           vbOKOnly + vbInformation, "Approval Entery (Date Format Issue)")
'Worksheets("Master Sheet").Protect "***"

End With
End Sub

Hi,

Use below codes where the error is appearing.

Cells(lNextRow, 1).Value = Cells(lNextRow - 1, 1).Value + 1

Regards
taps

Errro remains the same "Run-time error '13'.

Hi,

Error 13 is basically for data type mismatch.
Therefore, check the bold part of below code whether it is generating or giving numeric values or not.

Cells(lNextRow - 1, 1).Value + 1

If the cell contains non numeric values then the above error will appear.

Regards
taps

I did not understand what you are trying to tell me.

What i am trying to here is if i enter any record the serial number should generate automatically based on previous number. But it is giving error as i mentioned in post # 1 & 3

Hi,

Could you please attached the excel file?


or try this..

Cells(lNextRow, 1).Value = Val(Cells(lNextRow - 1, 1)) + 1

Regards
taps

Here you go. Please check the attachment. open file VB - form - save data button

Dear Friend,

Please apply my last formula. it is working successfully.

Cells(lNextRow, 1).Value = Val(Cells(lNextRow - 1, 1)) + 1

Regards
taps

hai taps thanks for your help it is working now.

I have another issue. I am try to add validation. If any field is empty of any of the checkox is not select then user should get a message for that particular field. Means if the user did not fill any information of leave the field blank then the message should appear. But it is not working for me any idea on this issue from your side.

Hi,

You can use codes like this..

If CheckBox1.Value = False And CheckBox2.Value = False And CheckBox3.Value = False And CheckBox4.Value = False And CheckBox5.Value = False And CheckBox6.Value = False Then
MsgBox "...................."
End If

for textboxes you can use codes like this..

If TextBox1 = "" And TextBox2= "" .......... Then
MsgBox "................."
End If

Regards
taps

Thanks for your reply if possible there is another issue i have tried but fail. I am trying to display a message if the textbox5 date is less than textbox3 date. Below is the code.

Problem Is Here:

If Not IsDate(TextBox5) < IsDate(TextBox3) Then
            MsgBox "Replied date cannot be greater than Received date."
            TextBox5.Text = ""
            TextBox5.SetFocus
             Cancel = True
            End If

Full Code:

Private Sub TextBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
'check date format
With TextBox5
    If Len(.Text) <> 10 Or _
        Mid(.Text, 3, 1) <> "/" Or _
            Mid(.Text, 6, 1) <> "/" Then
                response = MsgBox("Please check your Date format. It must be in 'dd/mm/yyyy'" _
                        + vbCrLf + " " + vbCrLf + " Abdul Aleem - Lets Make Life Easier...", _
                            vbOKOnly + vbCritical, "Approval Entery (Date Format Issue)")
                                   TextBox5 = ""
                                      TextBox5.SetFocus
                                Cancel = True
                            Exit Sub
                        End If
                    End With
                If Not IsDate(TextBox5) Or Mid(TextBox5, 1, 2) > 32 Then
            MsgBox "Plese enter a valid date."
            
            If Not IsDate(TextBox5) < IsDate(TextBox3) Then
            MsgBox "Replied date cannot be greater than Received date."
            TextBox5.Text = ""
            TextBox5.SetFocus
             Cancel = True
            End If
            
        Cancel = True
      Exit Sub
End If
End Sub

Thanks for your help Mr Taps. It works for me with small correction. I hope the below is the final issue from my side to close this topic.

I have posted my script in post#1. I am looking for a value i.e. Visa No (Column B Values) in a active sheet only. I want to check the visa no complete workbook and display a message as below. For your suggestion and correction please.

Here is the code:

'Check if Visa No exits in database.
    If Not rng.find(TextBox1, , LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ 
    SearchDirection:=xlPrevious, MatchCase:=False) Is Nothing Then
    response = MsgBox("Visa No. '" & TextBox1 & " 'already exits in database" _
    + vbCrLf + " " + vbCrLf + "  - Lets Make Life Easier...", _
    vbOKOnly + vbInformation, "Approval Entry (Date Format Issue)")
    Exit Sub
    End If

Hi,

Not able to get your problem. Please let me know..
1. Which sheet(s) contain visa number?
2. Is it placed in entire column or in a particular cell?
3. Where you would like to find the visa number? Only current sheet or entire workbook?
4. What are the problem with current code you have mentioned above?
5. How you wish to modify it?

Regards
taps

sorry for incomplete information. Here are the details.

1. Visa number will be entered in column B on entire workbook.
2. it is entered in column B i.e. let say visa no. 100 is entered in column B, can be in any cell but not more than one time. (Unique value)
3. i want to find in entire workbook.
4. my current code is looking in current sheet not complete workbook.
5. in post # 1 i have provide you the complete script and post # 7 i have attached the file.

I hope the above information is enough for your understand my problem.

Hi,

Use below codes..

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate

'Check if Visa No exits in database.
    If Not rng.find(TextBox1, , LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ 
    SearchDirection:=xlPrevious, MatchCase:=False) Is Nothing Then
    response = MsgBox("Visa No. '" & TextBox1 & " 'already exits in database" _
    + vbCrLf + " " + vbCrLf + "  - Lets Make Life Easier...", _
    vbOKOnly + vbInformation, "Approval Entry (Date Format Issue)")
    Exit Sub
    End If
  Next

Regards
taps

Hai taps thanks for your time and help. I will marked this topic as closed if i have any issue i will come back to you with this thread.

Topic marked as solved and reputation is added.

You are most welcome