Option Explicit
Dim bexitflag As Boolean

Private Sub CmdAckPrint_Click()
    SaveTextBoxValueToWord
End Sub

Private Sub CmdAdd_Click()
     txtFieldsEnabled
     Me.TxtCustID.SetFocus
     CmdAdd.Enabled = False
     CmdSave.Enabled = True
End Sub

Private Sub CmdCancel_Click()
    TxtBoxBlank
    txtFieldsDisabled
    CmdSave.Enabled = False
    CmdAdd.Enabled = True
End Sub

Private Sub CmdReview_Click()

Dim reviewText As String

reviewText = "Please check Record before Saving data..." & vbNewLine & _
        "Customer ID    : " & TxtCustID.Text & vbNewLine & _
        "Customer Name  : " & TxtCustName.Text & vbNewLine & _
        "Customer Address: " & TxtCustAdd.Text & vbNewLine & _
        "Customer City   : " & TxtCustCity.Text & vbNewLine & _
        "Customer State  : " & StatesList.Value & vbNewLine & _
        "Customer PIN    : " & TxtCustPin.Text & vbNewLine & _
        "Customer Phone  : " & TxtCustPhone.Text & vbNewLine & _
        "Customer Email  : " & TxtCustEmail.Text & vbNewLine & _
        "Customer PAN    : " & TxtCustPan.Text & vbNewLine & _
        "Customer State Code: " & TxtCustStateCode.Text & vbNewLine & _
        "Customer GSTIN  : " & TxtCustGstin.Text & vbNewLine & _
        "SRID            : " & TxtSRID.Text
        
        MsgBox reviewText, vbExclamation, "Please Check Data... Gautam Banerjee"
        
End Sub

Private Sub CmdDataReview_Click()
    MsgBoxForm
End Sub

Private Sub CMmdPPT_Click()
    CreatePowerPointPresentation
End Sub

Private Sub CmdMsgboxText_Click()
     Dim reviewText As String
     reviewText = "Review the entered data:" & vbNewLine & _
                 "Customer ID: " & TxtCustID.Text & vbNewLine & _
                 "Customer Name: " & TxtCustName.Text & vbNewLine & _
                 "Customer Address: " & TxtCustAdd.Text & vbNewLine & _
                 "Customer City: " & TxtCustCity.Text & vbNewLine & _
                 "Customer State: " & StatesList.Value & vbNewLine & _
                 "Customer PIN: " & TxtCustPin.Text & vbNewLine & _
                 "Customer Phone: " & TxtCustPhone.Text & vbNewLine & _
                 "Customer Email: " & TxtCustEmail.Text & vbNewLine & _
                 "Customer PAN: " & TxtCustPan.Text & vbNewLine & _
                 "Customer State Code: " & TxtCustStateCode.Text & vbNewLine & _
                 "Customer GSTIN: " & TxtCustGstin.Text & vbNewLine & _
                 "SRID: " & TxtSRID.Text
    
    ' Display the review text in a message box for the user to confirm
    MsgBox reviewText, vbInformation, "Review Data"
End Sub

Sub SaveTextBoxValueToWordAsTableWithHeading()
    Dim wdApp As Object ' Word Application
    Dim wdDoc As Object ' Word Document
    Dim wdTable As Object ' Word Table
    Dim txtName, txtAdd, txtCity, txtPIN, txtState, txtMobile, txtEmail, txtPan, txtGST As String ' Value from UserForm TextBox
    
    ' Create a new instance of Word
    Set wdApp = CreateObject("Word.Application")
    
    ' Add a new document
    Set wdDoc = wdApp.Documents.Add
    
    ' Show the Word application
    wdApp.Visible = True
    
    ' Get the value from the UserForm TextBox
    txtName = custdataentryform.TxtCustName.Value
    txtAdd = custdataentryform.TxtCustAdd.Value
    txtCity = custdataentryform.TxtCustCity.Value
    txtPIN = custdataentryform.TxtCustPin.Value
    txtState = custdataentryform.StatesList.Value
    txtMobile = custdataentryform.TxtCustPhone.Value
    txtEmail = custdataentryform.TxtCustEmail.Value
    txtPan = custdataentryform.TxtCustPan.Value
    txtGST = custdataentryform.TxtCustGstin.Value

       
   
    ' Create a new table with 2 columns
    Set wdTable = wdDoc.Tables.Add(Range:=wdDoc.Content, NumRows:=11, NumColumns:=2)
    wdTable.Borders.enable = True ' Add borders to the table
    
    
    ' Insert data into the table cells
    
    wdTable.cell(1, 1).Range.Text = ""
    wdTable.cell(1, 2).Range.Text = ""
    wdTable.cell(2, 1).Range.Text = "Customer Name"
    wdTable.cell(2, 2).Range.Text = txtName
    wdTable.cell(3, 1).Range.Text = "Address"
    wdTable.cell(3, 2).Range.Text = txtAdd
    wdTable.cell(4, 1).Range.Text = "City"
    wdTable.cell(4, 2).Range.Text = txtCity
    wdTable.cell(5, 1).Range.Text = "PIN Code"
    wdTable.cell(5, 2).Range.Text = txtPIN
    wdTable.cell(6, 1).Range.Text = "State"
    wdTable.cell(6, 2).Range.Text = txtState
    wdTable.cell(7, 1).Range.Text = "Mobile No."
    wdTable.cell(7, 2).Range.Text = txtMobile
    wdTable.cell(8, 1).Range.Text = "E-mail Address"
    wdTable.cell(8, 2).Range.Text = txtEmail
    wdTable.cell(9, 1).Range.Text = "PAN Card No."
    wdTable.cell(9, 2).Range.Text = txtPan
    wdTable.cell(10, 1).Range.Text = "Customer GSTIN"
    wdTable.cell(10, 2).Range.Text = txtGST
    
    ' Insert heading lines before the tabl
    
    wdTable.Range.InsertBefore "***** Acknowledgment *****"
    wdTable.Range.InsertBefore "===============================" & vbNewLine
    wdTable.Range.InsertBefore "Mobile No. 123456XXXX,    E-mail : gincom1@yahoo.com" & vbNewLine
    wdTable.Range.InsertBefore "Dinajpur - 705006" & vbNewLine
    wdTable.Range.InsertBefore "NH 34, Balurghat," & vbNewLine
    wdTable.Range.InsertBefore "Banerjee Agencies" & vbNewLine
    wdTable.Range.InsertBefore "Banerjee Agencies" & vbNewLine
    wdTable.Range.InsertBefore "  " & vbNewLine
    wdDoc.InlineShapes.AddPicture "D:\ExcelVBA\GB.png" ' Replace with the path to your image
    wdTable.Range.InsertAfter "Please check the data, if you found any error," & vbNewLine
    wdTable.Range.InsertAfter "Contact us or our Sales Representative immediately"
        
    
    ' Save the Word document
    wdDoc.SaveAs "D:\ExcelVBA\ReceiptWithTableAndHeading.docx" ' Replace with the desired file path
    
    ' Clean up
    Set wdTable = Nothing
    Set wdDoc = Nothing
    Set wdApp = Nothing
End Sub

Sub SaveTextBoxValueToWord()
    Dim wdApp As Object ' Word Application
    Dim wdDoc As Object ' Word Document
    Dim txtName, txtAdd, txtCity, txtPIN, txtState, txtMobile, txtEmail, txtPan, txtGST As String ' Value from UserForm TextBox
    
    ' Create a new instance of Word
    Set wdApp = CreateObject("Word.Application")
    
    ' Add a new document
    Set wdDoc = wdApp.Documents.Add
    
    ' Show the Word application
    wdApp.Visible = True
    
    ' Get the value from the UserForm TextBox
    txtName = custdataentryform.TxtCustName.Value
    txtAdd = custdataentryform.TxtCustAdd.Value
    txtCity = custdataentryform.TxtCustCity.Value
    txtPIN = custdataentryform.TxtCustPin.Value
    txtState = custdataentryform.StatesList.Value
    txtMobile = custdataentryform.TxtCustPhone.Value
    txtEmail = custdataentryform.TxtCustEmail.Value
    txtPan = custdataentryform.TxtCustPan.Value
    txtGST = custdataentryform.TxtCustGstin.Value
    
        
    ' Insert logo or picture
    wdDoc.InlineShapes.AddPicture "D:\ExcelVBA\GB.png"  ' Replace with the path to your image
    
    With wdDoc.Content
        ' Center align and set font size
        .ParagraphFormat.Alignment = 1 ' Center alignment
        .Font.Size = 24
       
    wdDoc.Content.InsertAfter "" & vbNewLine
    wdDoc.Content.InsertAfter "Banerjee Agencies" & vbNewLine
    wdDoc.Content.InsertAfter "NH 34, Balurghat," & vbNewLine
    wdDoc.Content.InsertAfter "Dinajpur - 705006" & vbNewLine
    wdDoc.Content.InsertAfter "Mobile No. 123456XXXX,    E-mail : gincom1@yahoo.com" & vbNewLine
    wdDoc.Content.InsertAfter "====================================================" & vbNewLine & vbNewLine
    wdDoc.Content.InsertAfter "**************     Acknowledgment     ****************" & vbNewLine & vbNewLine
      
     .ParagraphFormat.Alignment = 0 ' Left alignment
        .Font.Size = 11
        
    ' Insert the value into the Word document
    wdDoc.Content.InsertAfter "Customer Name : " & txtName & vbNewLine
    wdDoc.Content.InsertAfter "Address : " & txtAdd & vbNewLine
    wdDoc.Content.InsertAfter "City : " & txtCity & vbNewLine
    wdDoc.Content.InsertAfter "PIN Code : " & txtPIN & vbNewLine
    wdDoc.Content.InsertAfter "State : " & txtState & vbNewLine
    wdDoc.Content.InsertAfter "Mobile No. : " & txtMobile & vbNewLine
    wdDoc.Content.InsertAfter "E-mail Address : " & txtEmail & vbNewLine
    wdDoc.Content.InsertAfter "PAN Card No. : " & txtPan & vbNewLine
    wdDoc.Content.InsertAfter "Customer GSTIN : " & txtGST & vbNewLine
    
     '.ParagraphFormat.Alignment = 1 ' Center alignment
      '  .Font.Size = 11
    
    wdDoc.Content.InsertAfter "====================================================" & vbNewLine
    wdDoc.Content.InsertAfter "Note : If you found any error, Please inform us     " & vbNewLine
    wdDoc.Content.InsertAfter "Thanking you     "
    
     End With
    ' Save the Word document
    wdDoc.SaveAs "D:\ExcelVBA\Receipt.docx" ' Replace with the desired file path
    
    ' Clean up
    Set wdDoc = Nothing
    Set wdApp = Nothing
End Sub

Private Sub CmdPrint_Click()
    SaveTextBoxValueToWordAsTableWithHeading
End Sub

Private Sub CmdSave_Click()

    Dim newCustID As String
    newCustID = TxtCustID.Value
    
    If CustIDExists(newCustID) Then
        MsgBox "Cust_ID already exists. Please enter a unique Cust_ID.", vbExclamation, "Duplicate Cust-ID... Gautam Banerjee"
        Exit Sub
    End If
    
    SaveDatatoExcel
    SaveDatatoAccess
    
    CmdSave.Enabled = False
    CmdAdd.Enabled = True
    TxtBoxBlank

End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub CustSC_Click()
Label40.Caption = "Creditors"
End Sub

Private Sub CustSD_Click()
    Label40.Caption = "Debitors"
End Sub

Private Sub StatesList_Click()
    Dim SelectedStates As String
    SelectedStates = StatesList.Value
    
    Dim StateCode As String
    StateCode = GetStateGSTIN(SelectedStates)
    
    TxtCustStateCode.Value = StateCode
    
    Instruction.BackColor = vbRed
    Instruction.Caption = "Phone should be 10 characters long and must be numeric"
    
    Label33.Caption = Me.TxtCustPin.Text
    
End Sub

Private Function GetStateGSTIN(ByVal stateName As String) As String

Select Case stateName

    Case "Andhra Pradesh": GetStateGSTIN = "37"
    Case "Arunachal Pradesh": GetStateGSTIN = "12"
    Case "Assam": GetStateGSTIN = "18"
    Case "Bihar": GetStateGSTIN = "10"
    Case "Chhattisgarh": GetStateGSTIN = "22"
    Case "Goa": GetStateGSTIN = "30"
    Case "Gujarat": GetStateGSTIN = "24"
    Case "Haryana": GetStateGSTIN = "06"
    Case "Himachal Pradesh": GetStateGSTIN = "02"
    Case "Jharkhand": GetStateGSTIN = "20"
    Case "Karnataka": GetStateGSTIN = "29"
    Case "Kerala": GetStateGSTIN = "32"
    Case "Madhya Pradesh": GetStateGSTIN = "23"
    Case "Maharashtra": GetStateGSTIN = "27"
    Case "Manipur": GetStateGSTIN = "14"
    Case "Meghalaya": GetStateGSTIN = "17"
    Case "Mizoram": GetStateGSTIN = "15"
    Case "Nagaland": GetStateGSTIN = "13"
    Case "Odisha": GetStateGSTIN = "21"
    Case "Punjab": GetStateGSTIN = "03"
    Case "Rajasthan": GetStateGSTIN = "08"
    Case "Sikkim": GetStateGSTIN = "11"
    Case "Tamil Nadu": GetStateGSTIN = "33"
    Case "Telangana": GetStateGSTIN = "36"
    Case "Tripura": GetStateGSTIN = "16"
    Case "Uttar Pradesh": GetStateGSTIN = "09"
    Case "Uttarakhand": GetStateGSTIN = "05"
    Case "West Bengal": GetStateGSTIN = "19"
    Case "Andaman and Nicobar Islands": GetStateGSTIN = "35"
    Case "Chandigarh": GetStateGSTIN = "04"
    Case "Delhi": GetStateGSTIN = "07"
    Case "Dadra and Nagar Haveli and Daman and Diu": GetStateGSTIN = "26"
    Case "Jammu and Kashmir": GetStateGSTIN = "01"
    Case "Ladakh": GetStateGSTIN = "38"
    Case "Lakshadweep": GetStateGSTIN = "31"
    Case "Puducherry": GetStateGSTIN = "34"

End Select

End Function

Private Sub StatesList_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If StatesList.ListIndex = -1 Then
        MsgBox "Please select a state from the list", vbExclamation, "Invalid input... Gautam Banerjee"
        Cancel = True
        Me.StatesList.SetFocus
    End If
    
End Sub

Private Sub StatesList_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If StatesList.ListIndex = -1 Then
        MsgBox "Please select a state from the list", vbExclamation, "Invalid input... Gautam Banerjee"
        KeyAscii = 0
        Me.StatesList.SetFocus
    End If
End Sub

Private Sub TxtCustAdd_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim address As String
    address = Trim(Me.TxtCustAdd.Text)
    
    If Len(address) < 5 Then
       MsgBox "Customer Address should be at least 5 characters long.", vbExclamation, "Invalid input... Gautam Banerjee"
       Cancel = True
       Me.TxtCustAdd.SetFocus
    End If
    
    Label30.Caption = Me.TxtCustName.Text
     
End Sub

Private Sub TxtCustAdd_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    Instruction.BackColor = vbRed
    Instruction.Caption = "Address should be minimum 5 Characters"
End Sub

Private Sub TxtCustcity_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim City As String
    City = Trim(Me.TxtCustCity.Text)
    
    If Len(City) < 3 Then
       MsgBox "Customer City should be at least 3 characters long.", vbExclamation, "Invalid input... Gautam Banerjee"
       Cancel = True
       Me.TxtCustCity.SetFocus
    End If
    
    Label31.Caption = Me.TxtCustAdd.Text
    
End Sub

Private Function IsValidEmail9(ByVal Email As String) As Boolean
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    
    regex.Pattern = "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$"
    IsValidEmail = regex.test(Email)
        
End Function

Private Sub TxtCustCity_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    Instruction.BackColor = vbYellow
    Instruction.ForeColor = vbBlue
    Instruction.Caption = "City should be minimum 3 Characters"
End Sub

Private Sub TxtCustEmail_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim Email As String
    Email = Me.TxtCustEmail.Text
    
    If Not IsValidEmail(Email) Then
        MsgBox "Please enter a valid email address", vbExclamation, "Invalid Email...Gautam Banerjee"
        Cancel = True
        Me.TxtCustEmail.SetFocus
    End If
    
    Label35.Caption = Me.TxtCustPhone.Text
    
End Sub

Private Function IsValidEmail(ByVal Email As String) As Boolean
    
    Dim regex As Object
    Set regex = CreateObject("Vbscript.regExp")
    
    regex.Pattern = "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$"
    
    IsValidEmail = regex.test(Email)
    
End Function

Private Sub TxtCustEmail_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    Instruction.BackColor = vbBlue
    Instruction.Caption = "Email should be right E-mail Format (i.e.gincom1@yahoo.com)"

End Sub

Private Sub TxtCustGstin_Exit(ByVal Cancel As MSForms.ReturnBoolean)
     Instruction.Caption = "Please Fill First two characters must 'SR' and rest of the two must be Digit"
     Label39.Caption = Me.TxtCustGstin.Text
End Sub

Private Sub TxtCustGstin_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Instruction.Caption = "Type only last 3 Characters"
End Sub

Private Sub TxtCustID_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
     ' Restrict Cust_ID to three characters: 1 alphabet + 2 numeric
    If Len(Me.TxtCustID.Value) = 0 Then
    
        ' Allow only uppercase alphabets as the first character
        If Not (KeyAscii >= 65 And KeyAscii <= 90) Then
            KeyAscii = 0 ' Cancel the keypress
            MsgBox "Please enter an uppercase alphabet as the first character.", vbExclamation, "Invalid Input...Gautam Banerjee"
        End If
    ElseIf Len(Me.TxtCustID.Value) = 1 Or Len(Me.TxtCustID.Value) = 2 Then
        ' Allow only numeric characters for the second and third characters
        If Not (KeyAscii >= 48 And KeyAscii <= 57) Then
            KeyAscii = 0 ' Cancel the keypress
            MsgBox "Please enter numeric digits for the second and third characters.", vbExclamation, "Invalid Input...Gautam Banerjee"
        End If
    ElseIf Len(Me.TxtCustID.Value) >= 3 Then
        ' Prevent entering more than 3 characters
        KeyAscii = 0 ' Cancel the keypress
        MsgBox "Cust_ID should be exactly 3 characters.", vbExclamation, "Invalid Input...Gautam Banerjee"
    End If
End Sub

 

Function IsLetter(ByVal s As String) As Boolean
    IsLetter = (Asc(UCase(s)) >= 65 And Asc(UCase(s)) <= 90)
End Function

Private Sub TxtCustName_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim CName As String
    CName = Trim(Me.TxtCustName.Text)
    
    Instruction.Caption = "Name should be minimum 3 Characters"

    If Len(CName) < 3 Then
       MsgBox "Customer name Can not Blank", vbExclamation, "Invalid input... Gautam Banerjee"
       Cancel = True
       Me.TxtCustName.SetFocus
    End If
    
     Label29.Caption = Me.TxtCustID.Text
    
End Sub

Private Sub TxtCustName_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Instruction.Caption = "Name should be minimum 3 Characters"
    Instruction.BackColor = vbBlue
End Sub

Private Sub TxtCustPan_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim validChars As String
    Dim pan As String
    
    Instruction.BackColor = vbRed
    Instruction.Caption = "PAN Card No. should be right format, All letters must type in Uppercase (i.e ABCDE9999M)"

    ' Handle the Enter key separately
    If KeyAscii = 13 Then
        KeyAscii = 0 ' Cancel the keypress
        Exit Sub
    End If

    validChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"
    pan = UCase(Trim(Me.TxtCustPan.Text)) ' Convert input to uppercase

    ' Check if the entered character is a valid character (A-Z, 0-9)
    If InStr(validChars, Chr(KeyAscii)) = 0 Then
        KeyAscii = 0 ' Cancel the keypress
        MsgBox "PAN No. should only contain uppercase letters (A-Z) and digits (0-9).", vbExclamation, "Invalid Input"
    End If

    ' Validate the PAN No. format after 5 letters and 4 digits
    If Len(pan) < 5 And KeyAscii >= 48 And KeyAscii <= 57 Then
        KeyAscii = 0 ' Cancel the keypress
        MsgBox "Invalid PAN format. The first 5 characters should be uppercase letters (A-Z).", vbExclamation, "Invalid Input"
    ElseIf Len(pan) = 5 And Not (KeyAscii >= 48 And KeyAscii <= 57) Then
        KeyAscii = 0 ' Cancel the keypress
        MsgBox "Invalid PAN format. The next 4 characters should be digits (0-9).", vbExclamation, "Invalid Input"
    ElseIf Len(pan) = 9 And (KeyAscii >= 48 And KeyAscii <= 57) Then
        KeyAscii = 0 ' Cancel the keypress
        MsgBox "Invalid PAN format. The last character should be an uppercase letter (A-Z).", vbExclamation, "Invalid Input"
    End If
    
    Label36.Caption = Me.TxtCustEmail.Text
    
End Sub

 

Private Sub TxtCustPhone_Exit(ByVal Cancel As MSForms.ReturnBoolean)
     Dim Fone As String
     Fone = Me.TxtCustPhone.Text
     
     If Len(Fone) <> 10 Or Not IsNumeric(Fone) Then
        MsgBox "Please enter valid Mobile no. (10 Digit)", vbExclamation, "Invalid input...Gautam Banerjee"
        Cancel = True
        Me.TxtCustPhone.SetFocus
    End If
    
    Label34.Caption = Me.StatesList.Text
    
End Sub

Private Sub TxtCustPin_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim Pin As String
    Pin = Me.TxtCustPin.Text
    
   If Len(Pin) <> 6 Or Not IsNumeric(Pin) Then
        MsgBox "Customer PIN Code should be exactly 6 digits and all numeric", vbExclamation, "Invalid input... Gautam Banerjee"
        Cancel = True
        Me.TxtCustPin.SetFocus
    End If
   
   Label32.Caption = Me.TxtCustCity.Text
   
End Sub

Private Sub TxtCustPin_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    Instruction.BackColor = vbBlue
    Instruction.ForeColor = vbWhite
    Instruction.Caption = "PIN code should be minimum 6 Characters long and all must be Digit"
End Sub

Private Sub TxtCustStateCode_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Not bexitflag Then
        ' Get the values of TxtCustPAN and TxtCustStateCode
        Dim PanNo As String
        Dim StateCode As String
        
        PanNo = Trim(Me.TxtCustPan.Text)
        StateCode = Trim(Me.TxtCustStateCode.Text)
        
        ' Combine the values of TxtCustPAN and TxtCustStateCode to form GSTIN
        Dim gstin As String
        gstin = StateCode & PanNo
        
        ' Update the TxtCustGstin field
       Me.TxtCustGstin.Text = ""
       Me.TxtCustGstin.Text = gstin
        
        ' Move the cursor to the end of the TxtCustGstin field
        Me.TxtCustGstin.SetFocus
        Me.TxtCustGstin.SelStart = Len(gstin)
    End If
    
    ' Reset the flag
    bexitflag = False
    
    Label38.Caption = Me.TxtCustPan.Text
    Label39.Caption = Me.TxtCustGstin.Text
End Sub

Private Sub TxtCustStateCode_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    
    If KeyCode = vbKeyReturn Then
         bexitflag = True
         Me.TxtCustStateCode.SelStart = Len(Me.TxtCustStateCode.Text)
         Me.TxtCustStateCode.SelLength = 0
    End If
    
    Instruction.BackColor = vbRed
    Instruction.Caption = "Please fill last 3 Characters in Customer GSTIN field (i.e 1Z2)"

End Sub

Private Sub TxtSRID_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim Srid As String

Srid = UCase(Trim(TxtSRID.Text))

If Len(Srid) <> 4 Or Left(Srid, 2) <> "SR" Or Not IsNumeric(Mid(Srid, 3, 2)) Then
    MsgBox "Invalid SRID format. It should start with 'SR' followed by two numeric characters", vbExclamation, "Invalid input"
    Cancel = True
    Me.TxtSRID.SetFocus
End If

Label37.Caption = Srid

End Sub

Sub PopulateStatesList()

    Dim statesArray As Variant
    
    statesArray = Array("Andhra Pradesh", "Arunachal Pradesh", "Assam", "Bihar", "Chhattisgarh", "Goa", "Gujarat", _
        "Haryana", "Himachal Pradesh", "Jharkhand", "Karnataka", "Kerala", "Madhya Pradesh", "Maharashtra", _
        "Manipur", "Meghalaya", "Mizoram", "Nagaland", "Odisha", "Punjab", "Rajasthan", "Sikkim", _
        "Tamil Nadu", "Telangana", "Tripura", "Uttar Pradesh", "Uttarakhand", "West Bengal", "Andaman and Nicobar Islands", _
        "Chandigarh", "Delhi", "Dadra and Nagar Haveli and Daman and Diu", "Jammu and Kashmir", "Ladakh", "Lakshadweep", "Puducherry")
        
    Me.StatesList.Clear
    
    Me.StatesList.List = statesArray

End Sub

Sub MsgBoxForm()

Dim MsgDataForm As Object

Set MsgDataForm = New ReviewDataForm

MsgDataForm.Label15.Caption = TxtCustID.Value
MsgDataForm.Label16.Caption = TxtCustName.Value
MsgDataForm.Label17.Caption = TxtCustAdd.Value
MsgDataForm.Label18.Caption = TxtCustCity.Value
MsgDataForm.Label19.Caption = TxtCustPin.Value
MsgDataForm.Label20.Caption = StatesList.Value
MsgDataForm.Label21.Caption = TxtCustPhone.Value
MsgDataForm.Label22.Caption = TxtCustEmail.Value
MsgDataForm.Label23.Caption = TxtCustPan.Value
MsgDataForm.Label24.Caption = TxtCustStateCode.Value
MsgDataForm.Label25.Caption = TxtCustGstin.Value

If CustSD.Value = True Then
    MsgDataForm.Label26.Caption = "Debitors"
Else
    MsgDataForm.Label26.Caption = "Creditors"
End If

MsgDataForm.Label27.Caption = TxtSRID.Value

MsgDataForm.Show

End Sub

Sub SaveAndPrintRecord()
    ' Save the record
    ' Your existing code to save the record goes here

    ' Print the record
    Dim MsgDataForm As Object
    Set MsgDataForm = New ReviewDataForm

    MsgDataForm.Label15.Caption = TxtCustID.Value
    MsgDataForm.Label16.Caption = TxtCustName.Value
    MsgDataForm.Label17.Caption = TxtCustAdd.Value
    MsgDataForm.Label18.Caption = TxtCustCity.Value
    MsgDataForm.Label19.Caption = TxtCustPin.Value
    MsgDataForm.Label20.Caption = StatesList.Value
    MsgDataForm.Label21.Caption = TxtCustPhone.Value
    MsgDataForm.Label22.Caption = TxtCustEmail.Value
    MsgDataForm.Label23.Caption = TxtCustPan.Value
    MsgDataForm.Label24.Caption = TxtCustStateCode.Value
    MsgDataForm.Label25.Caption = TxtCustGstin.Value

    If CustSD.Value = True Then
        MsgDataForm.Label26.Caption = "Debitors"
    Else
        MsgDataForm.Label26.Caption = "Creditors"
    End If

    MsgDataForm.Label27.Caption = TxtSRID.Value

    ' Print the form
    MsgDataForm.PrintForm

    ' Show the printed form
    MsgDataForm.Show
End Sub

Sub txtFieldsDisabled()

    TxtCustID.Enabled = False
    TxtCustName.Enabled = False
    TxtCustAdd.Enabled = False
    TxtCustCity.Enabled = False
    TxtCustPin.Enabled = False
    TxtCustPhone.Enabled = False
    TxtCustEmail.Enabled = False
    TxtCustPan.Enabled = False
    TxtCustStateCode.Enabled = False
    TxtCustGstin.Enabled = False
    TxtSRID.Enabled = False
    StatesList.Enabled = False
    CustSD.Enabled = False
    CustSC.Enabled = False
End Sub

Sub txtFieldsEnabled()

    TxtCustID.Enabled = True
    TxtCustName.Enabled = True
    TxtCustAdd.Enabled = True
    TxtCustCity.Enabled = True
    TxtCustPin.Enabled = True
    TxtCustPhone.Enabled = True
    TxtCustEmail.Enabled = True
    TxtCustPan.Enabled = True
    TxtCustStateCode.Enabled = True
    TxtCustGstin.Enabled = True
    TxtSRID.Enabled = True
    StatesList.Enabled = True
    CustSD.Enabled = True
    CustSC.Enabled = True
End Sub

Sub TxtBoxBlank()

    TxtCustID.Text = ""
    TxtCustName.Text = ""
    TxtCustAdd.Text = ""
    TxtCustCity.Text = ""
    TxtCustPin.Text = ""
    TxtCustPhone.Text = ""
    TxtCustEmail.Text = ""
    TxtCustPan.Text = ""
    TxtCustStateCode.Text = ""
    TxtCustGstin.Text = ""
    TxtSRID.Text = ""
    
    Label29.Caption = ""
    Label30.Caption = ""
    Label31.Caption = ""
    Label32.Caption = ""
    Label33.Caption = ""
    Label34.Caption = ""
    Label35.Caption = ""
    Label36.Caption = ""
    Label37.Caption = ""
    Label38.Caption = ""
    Label39.Caption = ""
    

End Sub

Sub TxtLength()

    TxtCustID.MaxLength = 3
    TxtCustName.MaxLength = 25
    TxtCustAdd.MaxLength = 30
    TxtCustCity.MaxLength = 25
    TxtCustPin.MaxLength = 6
    TxtCustPhone.MaxLength = 10
    TxtCustEmail.MaxLength = 40
    TxtCustPan.MaxLength = 10
    TxtCustStateCode.MaxLength = 2
    TxtCustGstin.MaxLength = 15
    TxtSRID.MaxLength = 4

End Sub

Private Sub SheetView()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Customer Master")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim rng As Range
    Set rng = ws.Range("A2:A" & lastRow) ' Assuming data starts from A2
    
    ListSheet.Clear
    ListSheet.List = rng.Value
End Sub

Private Sub UserForm_Initialize()
    
    CmdSave.Enabled = False
    txtFieldsDisabled
    TxtLength
    TxtBoxBlank
    PopulateStatesList
    
    lblDate.Caption = "Today is " & Format(Date, "dddd") & ", Date: " & Format(Date, "dd/mm/yyyy")
    

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Customer_Master")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim rng As Range
    Set rng = ws.Range("A2:M" & lastRow) ' Assuming data starts from A2
    
    ListBox1.Clear
    ListBox1.List = rng.Value
    
    
End Sub

Sub RefreshListBox()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Customer_Master")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim rng As Range
    Set rng = ws.Range("A2:M" & lastRow) ' Assuming data starts from A2
    
    ListBox1.Clear
    ListBox1.List = rng.Value
End Sub

 

Sub SaveDatatoExcel()

    Dim ws As Worksheet
    Dim lastRow As Long
    
    Set ws = ThisWorkbook.Sheets("Customer_Master")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    
    Dim newCustID As String
    newCustID = TxtCustID.Value
    
    ' Check if the new Cust_ID already exists
    If CustIDExists(newCustID) Then
        MsgBox "Cust_ID already exists. Please enter a unique Cust_ID.", vbExclamation, "Duplicate Cust_ID"
        Me.TxtCustID.SetFocus
        Exit Sub
    End If
   
      
    If Trim(TxtCustID.Text) = "" Or Trim(Me.TxtCustName.Text) = "" Or _
       Trim(Me.TxtCustCity.Text) = "" Or Trim(Me.TxtCustPin.Text) = "" Or _
       Trim(Me.StatesList.Text) = "" Or Trim(Me.TxtCustEmail.Text) = "" Or _
       Trim(Me.TxtCustPhone.Text) = "" Or Trim(Me.TxtCustPan.Text) = "" Or _
       Trim(Me.TxtCustStateCode.Text) = "" Or Trim(Me.TxtCustGstin.Text) = "" Or _
       Trim(Me.TxtSRID.Text) = "" Then
       
       MsgBox "Please fill in all the required fields... Gautam Banerjee", vbExclamation, "Incomplete Entry... Gautam Banerjee"
       Exit Sub
    End If
    
    Dim response As VbMsgBoxResult
    
    response = MsgBox("Are you sure all the entries are correct? Do you want to save the record?", vbYesNo + vbQuestion, "Confirm Save")
    
    If response = vbYes Then
         
         ws.Cells(lastRow, "A").Value = TxtCustID.Value
         ws.Cells(lastRow, "B").Value = TxtCustName.Value
         ws.Cells(lastRow, "C").Value = TxtCustAdd.Value
         ws.Cells(lastRow, "D").Value = TxtCustCity.Value
         ws.Cells(lastRow, "E").Value = TxtCustPin.Value
         ws.Cells(lastRow, "F").Value = StatesList.Value
         ws.Cells(lastRow, "G").Value = TxtCustPhone.Value
         ws.Cells(lastRow, "H").Value = TxtCustEmail.Value
         ws.Cells(lastRow, "I").Value = TxtCustPan.Value
         ws.Cells(lastRow, "J").Value = TxtCustStateCode.Value
         ws.Cells(lastRow, "K").Value = TxtCustGstin.Value
        
        If CustSD.Value = True Then
            ws.Cells(lastRow, "L").Value = "SD"
        Else
            ws.Cells(lastRow, "L").Value = "SC"
        End If
        
        ws.Cells(lastRow, "M").Value = TxtSRID.Value
        ws.Cells(lastRow, "N").Value = ""
        
        MsgBox "Record saved successfully", vbExclamation, "Success...Gautam Banerjee"
        
    Else
        
        MsgBox "Please make the necessary changes and try again", vbInformation, "Changes Required...Gautam Banerjee"
    End If
        
    RefreshListBox
        
    'AccessDataSave  'Save Access Data
        
End Sub

Sub SaveDatatoAccess()

    Dim conn As Object
    Set conn = CreateObject("ADODB.connection")
    
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\AccessData\Customer_Master.accdb"
    
    conn.Open
    
    Dim sql As String
    
    sql = "INSERT INTO Customer_Master (Cust_ID, Cust_Name, Cust_Address, Cust_City, Cust_PIN, Cust_State, " & _
          "Cust_Phone, Cust_Email, Cust_Pan, Cust_GSTIN, Cust_Type, SR_ID) " & _
          "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" ' Parameterized query

    Dim cmd As Object
    
    Set cmd = CreateObject("ADODB.command")
    
       
      
    With cmd
        .ActiveConnection = conn
        .CommandText = sql
        .CommandType = 1
        
                ' Add parameters and set their values
        .Parameters.Append .CreateParameter("@Cust_ID", 200, 1, 255, TxtCustID.Value) ' 200: adVarChar, 255: max length
        .Parameters.Append .CreateParameter("@Cust_Name", 200, 1, 255, TxtCustName.Value)
        .Parameters.Append .CreateParameter("@Cust_Address", 200, 1, 255, TxtCustAdd.Value)
        .Parameters.Append .CreateParameter("@Cust_City", 200, 1, 255, TxtCustCity.Value)
        .Parameters.Append .CreateParameter("@Cust_PIN", 200, 1, 255, TxtCustPin.Value)
        .Parameters.Append .CreateParameter("@Cust_State", 200, 1, 255, StatesList.Value)
        .Parameters.Append .CreateParameter("@Cust_Phone", 200, 1, 255, TxtCustPhone.Value)
        .Parameters.Append .CreateParameter("@Cust_Email", 200, 1, 255, TxtCustEmail.Value)
        .Parameters.Append .CreateParameter("@Cust_Pan", 200, 1, 255, TxtCustPan.Value)
        .Parameters.Append .CreateParameter("@Cust_GSTIN", 200, 1, 255, TxtCustGstin.Value)
        .Parameters.Append .CreateParameter("@Cust_Type", 200, 1, 255, CustSD.Value)
        .Parameters.Append .CreateParameter("@SR_ID", 200, 1, 255, TxtSRID.Value)
        
        .Execute
    End With
    
    conn.Close
    
    Set conn = Nothing
    Set cmd = Nothing
    
End Sub
 

Function CustIDExists(custID As String) As Boolean

     Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Customer_Master")

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Dim rng As Range
    Set rng = ws.Range("A2:A" & lastRow)

    Dim cell As Range
CustIDExists = False

For Each cell In rng

    If cell.Value = custID Then
        CustIDExists = True
        Exit Function
    End If
Next cell

End Function

​