inserting a new row using Worksheet_SelectionChange()

[Martin Perera]

Hi Guys

I am rather new to dealing with Macro's and VBA

I have a problem which I hope you could help me with.

I am building a balance sheet, with a set number of spaces for entrys. The aim is to set a macro or function sub whatever that fires when the last entry is entered. the macro will then insert a new row for the next entry.

I have managed to get the code to insert the required row by using a range("NewRow") reference, ie naming a cell in the spreadsheet. Unfortunetly it can only fire the once as the new row leaves the reference behind.

what I need is a way of promoting the named cell with the code so that EVERY time the user gets to the last row the macro fires

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 
    If Intersect(Range("NewRow"), Target) Is Nothing Then Exit Sub 'if nowt there do nothing

'NewRow is the named cell, where once changed the new row needs to go

    If IsEmpty(Range("NewRow")) Then Exit Sub 'on change insert row


    Application.EnableEvents = False 
     
    Range("NewRow").Offset(rowOffset:=1, columnOffset:=0).Select 

'offset because otherwise the data entered had a new row inserted above it

    Selection.EntireRow.insert 
    Range("NewRow").Offset(rowOffset:=1, columnOffset:=0).Select 
    Application.EnableEvents = True 
End Sub

hope this is clear

probably going about this all wrong

Martin

 

[Tazguy37]

I'm a bit confused as to why you need to insert rows. Why not just add a row at the end of all your current data? Can you give us a clearer picture of how your data is laid out, and what you happen when a new entry is needed, and where that information will go? Thanks!

 

[Martin Perera]

As to Y

the balance sheet is designed to be used by various collegues who are,shall we say scared of touching a button in case it blows up the comp...

yup they are still out there.....

the only cells that will be editable will be in four columns
:-> 1 will be to enter a code. (will be prompted to do this if the next cell changed)
:-> 2 will be for a date (if of a month different from previous row, prompt for start new month)
:-> 3 will be to enter a description of the expenditure,(again prompt if next cell is used and this one not got data in it)
:-> 4 will be for the actual numerical value of the expenditure.

all other cells will be protected.
the whole workbook, will be as automated as possible. It is not the aim to educate the user of the workbook in the use of excel, laudable an aim as that may be. As they have no interest in doing so. The aim is to make it as easy as possible for the user to enter data.

the total of each code group will be transfered to another sheet - monthly balance for which a graph will be created on an ongoing basis.

the balance sheet will look initially like an A5 card size, with room for 5 or 6 entrys, surrounded by a black background, similar in style to the balance sheet in excel templates. The major difference being that I am hoping to add greater functionality by automating the addition of a new line when the last entry is used, thus extending the 'A5 card' indefinetly for the months entries.

Assuming the user has entered data correctly in all cells till last row.
user enters code, date, description, (at this point the macro fires new line inserted)user enters numeric value.

the new row will be beneath the row containing the above data, therefore not affecting the data entered by the user.

the problem is to figure out a reliable, reuseable trigger for this to happen. then to figure out the problems associated with dealing with a protected sheet, and extending the editable regions, but thats fun for later

I hope this makes it a little clearer as to the layout and aim of the sheet.

basically the users dont have the understanding to reliably enter a new row when they require one.