inserting a new row using Worksheet_SelectionChange()

Martin Perera

Board Regular
Joined
Jan 17, 2005
Messages
147
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

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

(y) Martin
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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

Board Regular
Joined
Jan 17, 2005
Messages
147
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...
:eek:
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 :rolleyes:

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.
 

Forum statistics

Threads
1,147,690
Messages
5,742,647
Members
423,746
Latest member
Joaogomes

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