Code and more code...

[Andrew_Rossington]

Hello.

My question is two-fold.

One: I have a table on a worksheet - simple stuff. I'm looking for some code to launch Data > Form (y'know, for adding new records and such). I tried the excel help, but it's not very helpful. Would anyone be able to point me in the right direction?

Two: Based on the above need, does anyone know how to automatically populate a column with a consecutive number for each new record added? (Kind of like a unique ID for each record, whereby if the latest record is number 5, I add a new one and this column has number 6 automatically entered.)

I'd be very grateful for any help with this.

Thanks.

 

[ADAMC]

question 1:

ActiveSheet.ShowDataForm

 

[Andrew_Rossington]

question 1:

ActiveSheet.ShowDataForm

Thanks for the suggestion my friend.

It's returning the "ShowDataForm method of worksheet class failed" error message.

 

[texasalynn]

for the row unique ID a couple of ways
1. Row()
if you have header rows, then on the formula add Row()+1 or how ever many header rows you have
2. A5+1
Or you could use this where the row up has the ID number

 

[Andrew_Rossington]

for the row unique ID a couple of ways
1. Row()
if you have header rows, then on the formula add Row()+1 or how ever many header rows you have
2. A5+1
Or you could use this where the row up has the ID number

I thank you for your help.

How would I add this to code which executes every time I add a new record?

 

[Andrew_Rossington]

I'm learning for myself, you'll all be pleased to know.

I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("B4").Value = "" Then

Range("A4") = ""

Else

Range("A4").Formula = "=Row() - 3"

End If

End Sub

Now how does one get that to loop through each cell in column B to check for a value?

 

[texasalynn]

How would I add this to code which executes every time I add a new record?

how are you adding records?

 

[brian.wethington]

Are you sure you want this to run every time you change the selection or every time you make a change to a cell?

At any rate

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Ce As Range, LstRw As Long

LstRw = Cells(Rows.Count, "B").End(xlUp).Row
For Each Ce In Range("B4:B" & LstRw)
    If Len(Ce.Value) = 0 Then
        Range("A" & Ce.Row).Value = vbNullString
    Else
        Range("A" & Ce.Row).Formula = "=Row() - 3"
    End If
Next Ce

End Sub

Hope this helps!

 

[texasalynn]

that is so weird you replied with what I was asking but I didn't see it. But anyway it looks like Brian answered you

 

[Andrew_Rossington]

Are you sure you want this to run every time you change the selection or every time you make a change to a cell?

At any rate

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Ce As Range, LstRw As Long

LstRw = Cells(Rows.Count, "B").End(xlUp).Row
For Each Ce In Range("B4:B" & LstRw)
    If Len(Ce.Value) = 0 Then
        Range("A" & Ce.Row).Value = vbNullString
    Else
        Range("A" & Ce.Row).Formula = "=Row() - 3"
    End If
Next Ce

End Sub

Hope this helps!

That's perfect. Thank you very much for your assistance. Do you recommend any online reading about this that I may be able to utilise in future?

And thank you to texasalynn for taking the time to help.

It's very much appreciated.