Alert before overwriting cell 1

[tyhand]

Hi all,

eXcel has this cool function in the tool/options menu in the edit tab called 'alert before overwriting cells'.

I'm trying to do the same thing programatically using vba. Any ideas how? There is a boolean property called 'AlertBeforeOverwriting', but i can't get it work. Any alternative methods? Thanks in advance. Peace!

tyhand
john 3:16

 

[Nikita6003]

Hi tyhand,

not quite sure what you want to do but <code> Application.AlertBeforeOverwriting = True </code> will switch on the alert option. BUT this'll only work if a user is manually moving data to a cell which is already filled.

If you want to do the same in code, you'll have to check whether your destination cell is empty and, if not, use a msgbox alert

e.g. moving stuff from cell B1 to cell A1 & check before overwriting:

    Dim l_iAnswer As Integer
    Dim l_sStringToMove As String
    
    'Set your answer variable to YES
    'This is the default which'll be used anyway if the destination cell is empty
    l_iAnswer = vbYes
    
    'Stick the contents of cell B1 to a string variable
    l_sStringToMove = Cells(1, 2)
    
    'If the cell is NOT empty ask the user what (s)he wants
    If Cells(1, 1) <> &quot;&quot; Then l_iAnswer = MsgBox(&quot;Do you want to overwrite cell &quot; & Cells(1, 1).Address & &quot;?&quot;, vbYesNo + vbQuestion)
    
    'assign the contents of cell B1 to the destination cell if allowed
    If l_iAnswer = vbYes Then Cells(1, 1) = l_sStringToMove

Hope this is what you need (if not - sorry - had the office xmas party yesterday ;-))

Cheers
Nikki

 

[tyhand]

hi nikki,

Thanks. It's pretty much what i wanna do. In actuality,
I was trying to imitate (programatically) the action of
calling the AlertBeforeOverwriting property if the
destination cell I'm moving data to was filled.

You wrote:
>BUT this'll only work if a user is manually moving
>data to a cell which is already filled.

My guess is that eXcel is using an event procedure to
accomplish this manually. I tried
recording a macro capturing the actions of moving data
from cell b1 to cell a1 just to see what was
recorded. To my disappointment, the only thing recorded
was:
Range(&quot;B1&quot.Cut Destination:=Range(&quot;A1&quot
Range(&quot;A1&quot.Select

I thought that at least the inherent Alert Box that
appears when doing this action would be recorded, but
it wasn't. Still curious to know how it works.

At any rate, I'll try your recommend lines of code.
Thanks again. Peace!