By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 916 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

How to validate excel textbox values using Access VBA

100+
P: 126
Hello everyone, the following Access VBA code opens an excel file and creates textboxes in a given range of cells dynamically. The code attaches "MouseUP" and "Exit" events to the textboxes (using the following "DateTextBox" class module).

Whenever a user enters in to the textbox, the code displays "Please enter date in mm/dd/yyyy format (for ex: 01/01/2009)." message which is accomplished using "MouseUp" event.

And, I used "Exit" event to validate the value that the user entered in to the textbox and display an error message if the user enter a date in past.

But somehow the "Exit" event is not working.

Please kindly let me know how to solve this issue?

Thanks.

1. Created an Access VBA module to open and create textboxes in an excel spreadsheet dynamically:

Expand|Select|Wrap|Line Numbers
  1. Public eXL As New eventsXL
  2.  
  3. Function Create_TextBox()
  4. Dim OLEObj As OLEObject
  5. Dim myRng As Range, myCell As Range
  6.  
  7.  
  8. With eXL
  9. If .XL Is Nothing Then Set .XL = New Excel.Application
  10. .XL.Visible = True
  11. .XL.Interactive = True
  12. Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
  13. Set .WS = .WB.Worksheets("Example")
  14. .WS.Activate
  15.  
  16. .XL.CommandBars("Control Toolbox").Visible = False
  17. .WS.OLEObjects.Delete
  18.  
  19. Set myRng = .WS.Range("A2:A4")
  20. For Each myCell In myRng.Cells
  21. With myCell
  22.     .NumberFormat = ";;;" 'hide the value in the cell
  23.     Set OLEObj = .Parent.OLEObjects.Add _
  24.                 (ClassType:="Forms.TextBox.1", Link:=False, _
  25.                 DisplayAsIcon:=False, _
  26.                 Top:=.Top, _
  27.                 Left:=.Left, _
  28.                 Width:=.Width, _
  29.                 Height:=.Height)
  30. End With
  31.  
  32. .AddTextBoxEvents OLEObj.Object
  33. Next myCell
  34. End With
  35. End Function
2. Created "eventsXL" class module to attach events to the created textboxes:

Expand|Select|Wrap|Line Numbers
  1. Public WithEvents XL As Excel.Application
  2. Public WithEvents WB As Excel.Workbook
  3. Public WithEvents WS As Excel.Worksheet
  4. Private myTxb As New VBA.Collection
  5.  
  6. Public Property Get myTextBoxes() As VBA.Collection
  7.     Set myTextBoxes = myTxb
  8. End Property
  9.  
  10. Public Function AddTextBoxEvents(obj As Variant) As DateTextBox
  11.     Set AddTextBoxEvents = New DateTextBox
  12.     Set AddTextBoxEvents.myTextBox = obj
  13.     Me.myTextBoxes.Add AddTextBoxEvents
  14. End Function
  15.  
  16. Private Sub XL_WorkbookBeforeClose(ByVal WB1 As Excel.Workbook, Cancel As Boolean)
  17. With XL.Workbooks
  18. XL.Quit
  19. Set eXL.XL = Nothing
  20. Set eXL.WB = Nothing
  21. Set eXL.WS = Nothing
  22. Set eXL = Nothing
  23. End With
  24. Exit Sub
  25. End Sub
3. Created "DateTextBox" class module to define events that needs to attached to the created date textboxes:

Expand|Select|Wrap|Line Numbers
  1. Public WithEvents txb As MSForms.TextBox
  2.  
  3. Public Property Get myTextBox() As MSForms.TextBox
  4. Set myTextBox = txb
  5. End Property
  6.  
  7. Public Property Set myTextBox(ByRef myText As MSForms.TextBox)
  8. Dim curDate As String
  9. Set txb = myText
  10. curDate = Format(Date, "mm/dd/yyyy")
  11. With txb
  12.     .Text = curDate
  13. End With
  14. End Property
  15.  
  16. Public Sub txb_MouseUP(ByVal Button As Integer, _
  17.          ByVal Shift As Integer, ByVal X As Single, _
  18.          ByVal Y As Single)
  19. MsgBox "Please enter date in mm/dd/yyyy format (for ex: 01/01/2009)."
  20. End Sub
  21.  
  22. Public Sub txb_Exit(Cancel As Integer)
  23. Dim curDate As String, mon As String, day As String, year As String
  24. curDate = Format(Date, "mm/dd/yyyy")
  25. dParts = Split(curDate, "/")
  26. txbParts = Split(txb.Text, "/")
  27.  
  28. If (txbParts(0) <= dParts(0)) And (txbParts(1) < dParts(1)) And (txbParts(2) <= dParts(2)) Then
  29. MsgBox "Please enter either the current date or the date in future!"
  30. End If
  31. End Sub
Sep 11 '08 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,342
Try using the following for your class code :
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Public WithEvents txb As MSForms.TextBox
  4.  
  5. Public Property Get myTextBox() As MSForms.TextBox
  6.   Set myTextBox = txb
  7. End Property
  8.  
  9. Public Property Set myTextBox(ByRef myText As MSForms.TextBox)
  10.   Set txb = myText
  11.   txb.Text = Format(Date, "mm/dd/yyyy")
  12. End Property
  13.  
  14. Public Sub txb_MouseUP(ByVal Button As Integer, _
  15.                        ByVal Shift As Integer, _
  16.                        ByVal X As Single, _
  17.                        ByVal Y As Single)
  18.   MsgBox "Please enter date in mm/dd/yyyy format (EG. 01/14/2009)."
  19. End Sub
  20.  
  21. Public Sub txb_Exit(Cancel As Integer)
  22.   If Date > CDate(txb.Text) Then
  23.     MsgBox "Please enter a date that is not in the past!"
  24.     Calcel = True
  25.   End If
  26. End Sub
NB. It doesn't validate that the data entered is a valid date.
Sep 11 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.