Code and more code...

Andrew_Rossington

Board Regular
Joined
Feb 12, 2007
Messages
121
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
question 1:

ActiveSheet.ShowDataForm
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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

Board Regular
Joined
Feb 12, 2007
Messages
121
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

Board Regular
Joined
Feb 12, 2007
Messages
121
I'm learning for myself, you'll all be pleased to know.

I have:

Code:
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

Well-known Member
Joined
May 19, 2002
Messages
8,458
How would I add this to code which executes every time I add a new record?

how are you adding records?
 

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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

Code:
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

Well-known Member
Joined
May 19, 2002
Messages
8,458
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

Board Regular
Joined
Feb 12, 2007
Messages
121
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

Code:
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.
 

Forum statistics

Threads
1,181,418
Messages
5,929,793
Members
436,694
Latest member
dpatete

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top