Verify a row is selected and renumbering the first field 3

[HomeGrowth]

In Excel, I put the code on a button to insert new row

Dim lngRow As Long 'find the last number row
'put in code to verify a row is selected because the code
'will not work.
lngRow = ActiveCell.EntireRow.Cells(1, 1).Row
ActiveCell.EntireRow.Insert '2008 0219 insert the row
Cells(lngRow + 2, 1).EntireRow.FillDown
'put in code to renumber the first field (A2=01; A3=02, A3=03, and so on until the last field IsEmpty).

Right now, I will need code for
1. Verify a row is selected before to insert new row below it.
2. Number the new row and then renumber the following first field with text number (i.e. A6=05, A7=06, A8=07 so on).

Thank you for your help.

 

[AnotherHiggins]

Rather than inserting a line, why not just add the new data to the end of the current table, then sort as desired to get everything in the right order?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Chance1234]

There may be a better way to do this, but if you want to check the user has selected a row and not a cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Mid(Target.Address, 2, 2) = Right(Target.Address, 2) Then
        MsgBox "Is  Row , row selected is " & Target.Row
    End If
    
    
End Sub

Chance,

F, G + 3MSTG

 

[SkipVought]

I would concur with John. INSERT/DELETE is a mini scourge, in many cases.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[HomeGrowth]

I couldn't agreed with you more, but my users want to able to insert a task, Excel will renumber the task (from the first field). That is why I need the VBA to do the magic.

So, I just the insert done

Dim lngRow As Long 'find the last number row
lngRow = ActiveCell.EntireRow.Cells(1, 1).Row
ActiveCell.EntireRow.Insert '2008 0219 insert the row

'starting with cell being inserted.
Range("A?").Select

Do
If IsEmpty(ActiveCell) = False Then
'What is the code to renumber
End If
Loop Until IsEmpty(ActiveCell) = True

I need to work on the codes above
1. fill the cell with next number based on the previous number.
2. do the loop to renumber for the rest of row (first field)

thank you.

 

[ck1999]

How about this to renumber col A

dim vrow, vrow2
Dim lngRow As Long 'find the last number row
  'put in code to verify a row is selected because the code
  'will not work.
  lngRow = ActiveCell.EntireRow.Cells(1, 1).Row
  vrow = ActiveCell.Row
  vrow2 = Range("a2").End(xlDown).Row
  
  ActiveCell.EntireRow.Insert '2008 0219 insert the row
    For counter = vrow To vrow2 + 1
       Cells(counter, 1) = counter - 1
  Next counter

Make sure vrow and vrow2 are determined BEFORE insert

ck1999

 

[HomeGrowth]

ck1999 thank you the code, I bet your code works. Anyway, I got my code working to renumber the first field. Here is my code.

Dim lngRow As Long
Dim lngNum As Long

lngNum = 1 'set the first number
lngRow = ActiveCell.EntireRow.Cells(1, 1).Row
ActiveCell.EntireRow.Insert

Cells(lngRow, 1).Select 'select the first cell just inserted
ActiveCell.FormulaR1C1 = Cells(lngRow - 1, 1) + lngNum 'Assign the next number
ActiveCell.Offset(1, 0).Select 'go to next select the next cell

Do 'Renumber the all cells
If IsEmpty(ActiveCell) = False Then
lngNum = lngNum + 1 'increment to 1
ActiveCell.FormulaR1C1 = Cells(lngRow - 1, 1) + lngNum
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

Right now, the only isue is asking the user whether you like to insert the selected row, before the code runs.

I don;t understand chance1234 's code above, Someone needs to shed some light for Worksheet_SelectionChange.

Thanks.

 

[Chance1234]

SelectionChange is a worksheet Event

It will trigger when the selection on a sheet changes.

You insert the code by openign up the editor and selceting the sheet you want the code to run.



Chance,

F, G + 3MSTG

 

[ck1999]

USe a msgbox

vreply = msgbox("Do you want to enter a new row?",vbyesno)

if vreply = 6 then
all (but the DIM statements) of your code here
end if

ck1999

 

[AnotherHiggins]

Two things:

1) To renumber the column A, just use the formula [COLOR=blue white]=Row()-1[/color]. That will always return "1" in row 2, "2" in row 3, etc.

2) If this is going to be done via code, how will the customer know the difference between a) inserting a row; and b) adding data to the end and resorting? Set application.ScreenUpdating to False and all that a user will see is the end result.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[HomeGrowth]

Thank you everyone, I've learned so much on this topic. Right now the project is completed. I have an excel spreadsheet template that the user allows to insert a row.

Thanks again!