Locking Macro-Assigned Pictures so not to corrupt file

Dom2012

Board Regular
Joined
Oct 2, 2006
Messages
77
Hi,

I have picture buttons I have assigend macros to. The first, when clicked on, adds a row. The second deletes a row.

If the user puts the cursor in the wrong cell before clicking on either 'button', they delete the wrong row and corrupt the file. They could only recover it by closing the file without saving changes, and re-opening it - which is not an ideal solution.

Is there a way to link the macro-assigned picture buttons to specific cells so that if the user has the cursor in the wrong cell, the buttons won't work and a message tells them what to do? The soltion would also have to refer to non-relative cells since, as a row is added or deleted, it changes the cell's number by +/- one.

Thanks,

Dom

PS. If this is not clear, let me know and I'll explain better!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Dom2012

Board Regular
Joined
Oct 2, 2006
Messages
77
ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-1, 0).Range("A1:AP1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1:AP1").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
ActiveCell.Offset(-2, 0).Range("A1:AP1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1:AP1").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
ActiveCell.Offset(2, 0).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C)"
ActiveCell.Offset(-2, 2).Range("A1:C1").Select
End Sub

It's part of an accounting program. What it does is add a row into the accounts sheet just above the Totals row. This allows users to add another row of content (product, quantity, unit cost, that sort of thing). The Totals row then automatically updates total cost to include this new row/item.

For it to work, the user has to place the cursor in the left cell above the Totals row. This way, the row is always added at the bottom of the sheet - above the Totals row.

The Del macro works in exactly the same way ... but in reverse.

Problems occur if the cursor is in the wrong cell. If it's in the Totals row, this becomes a new product row. If the cursor is in this row when the Delete button is hit, the Totals row will be deleted.

Because I shall be coverting the spreadsheet into an .exe file later on, the Protection feature won't work properly, so I can't just lock cells.

What I need to do is make sure that the buttons only work if the cursor is in the left cell immediately above the Totals row. If it is not, a pop-up wanring should tell the user what they have done wrong.

Hope this makes sense!

Thanks,

Dom
 

Dom2012

Board Regular
Joined
Oct 2, 2006
Messages
77

ADVERTISEMENT

It's blank. It has occured to me that I could put unique text in it. The way the macro works right now though, it would copy whatever is in there into the new row, but this is easily fixed.

FOOTNOTE: Just realised you said Totals Row! I was expecting a solution might be linked to "unique text" in the left cell above the Totals row (which is where the cursor must be).

The Totals row does have unique text which is not copied that always begins with the words "Accounts total for". This can be changed if required.

Thanks.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Are all the cells below that cell blank? If so you can select the cell above like this:

Cells(Rows.Count, 1).End(xlUp).Select

If you put that at the start of your macro you will have the right ActiveCell.
 

Dom2012

Board Regular
Joined
Oct 2, 2006
Messages
77

ADVERTISEMENT

They aren't blank. The spreadsheet runs for about 1600 rows. This actually means the Totals row is a Sub-Total. There are about 30 Total Rows and I would need something unique about the cell top-left of each Totals row so that they - and only - they allow use of the Add/Del buttons.

I could put something like XXX in every one of them, but I would not know how to then link this to the buttons so they are active only when they 'see' XXX. Is this possible?

Thanks.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Is there an entry in every cell in column A above the Totals cell? If so you can use:

Cells(1, 1).End(xlDown).Select
 

Dom2012

Board Regular
Joined
Oct 2, 2006
Messages
77
There can be.

Could you tell me where in the macro I would put this line? I assume near the start - I'm totally unfamiliar with using VBA.

Thanks.
 

Forum statistics

Threads
1,136,308
Messages
5,674,993
Members
419,541
Latest member
freddyboots

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