Use Excel as Access
 

I would like to accomplish a couple of MS Access features in Excel:
1. Primary Key - automatic numbering of entries in rows without
replication allowance. Part of it is solved with data validation using
=COUNTIF function, right?
2. Date formatting - when you tab to the column for date entry, a guide
like "____-__-__" will popup and I just need to type in 20050721.
Please advice.


*** Sent via Developersdex http://www.developersdex.com ***

Use Excel as Access
 

(1) use the change event to test if a new row has been added to a table, the
event then can increment the "primary key" automatically....
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim newval As Long
'test if its in the table
If Range("C1").End(xlDown).Address = Target.Address Then

If Target.Offset(0, -1).Value = "" Then

newval = Target.Offset(-1, -1).Value + 1

Do While WorksheetFunction.CountIf(Range("B:B"), newval) 0
newval = newval + 1
Loop

Target.Offset(0, -1).Value = newval

End If

End If

End Sub

(2) use the selection change event to open a Userform - this can be set up
any way that you want.

"M H" wrote:

I would like to accomplish a couple of MS Access features in Excel:
1. Primary Key - automatic numbering of entries in rows without
replication allowance. Part of it is solved with data validation using
=COUNTIF function, right?
2. Date formatting - when you tab to the column for date entry, a guide
like "____-__-__" will popup and I just need to type in 20050721.
Please advice.


*** Sent via Developersdex http://www.developersdex.com ***

Use Excel as Access
 

Hi Pat,
Your code work superbly! Thanks!



*** Sent via Developersdex http://www.developersdex.com ***