Create Alert / Message in excel

G

gman1979

New Member
Joined
Oct 12, 2007
Messages
35
Hi,

I have created a number of data tracker in excel for my work, they are all linked in, due to some of the macro's contained within the workbooks I cannot protect them (such as hiding columns & rows). There are aprox 134 users that use these trackers on a daily basis. As a result there have been a few instances of formulas being typed over. What I am looking to do if on opening up, for an alert box to be displayed advising users not to overwrite any of the formulas as this can impact the real-time data they are trying to view. I had though of a semi-transparent text box, which upon clicking would hide, but I can’t figure out how to do this, and all the forums I have checked seems to only mention moving & resizing properties of the text box.

My second (and preferred) thought, would be a simple alert dialogue box to open every time the file is open which displays a message warning users not to type over any of the formulas. Any help with this would be greatly appreciated, even if it's something I haven’t thought of.

Thanks in advance

G
 
Sort by date Sort by votes
Welcome to the board. This will require some VBA code.

Right click the excel icon in top left corner by "file,edit,etc" - View Code

Paste the following

Code: 
Private Sub Workbook_Open()
MsgBox ("Do Not OverWrite Formulas")
End Sub
 
Upvote 0
Another alternative - to prevent overriding formula without worksheet protection, perhaps you can apply validation. Perhaps by setting the validation the same as the actual formula, that way only the result of the formula can apply.

Regards
Jon
 
Upvote 0
Nice Idea Jon...
Taking that a step further, you can run this macro to automatically put Each cell's formula as the data validation for that cell. So you don't have to manually do it for each formula....

Code: 
Sub advalidations()
For Each Cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
        With Cell.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Cell.Formula
        .IgnoreBlank = False
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = "Don't Overwrite My Formulas!!"
        .ShowInput = True
        .ShowError = True
    End With
Next Cell
End Sub
 
Upvote 0
Very good Jonmo1

Not wishing to overkill the suggested solutions, but I have suggested how you can still lock cells and not need to apply worksheet protection here.

This means that you can lock the cells and still prevent users from deleting the formula.

Using Jonmo1's similar method, you can group all formula and lock in one shot.

Select All (Ctrl+A) > Format > Cells > Protection > Uncheck "Locked"

then...

F5 > Special > Check Formulas

Format > Cells > Protection > Check 'Locked'

Hope this helps.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Reducing Decimal Places within an equation
Replies
3
Views
72
Joe4
Joe4
O
  • Question Question
Autofit Comment Box Height when specifying the Width or Autofit the Width when specifying the Height
Replies
0
Views
157
OilEconomist
O
K
  • Solved
Text box and formulas
Replies
2
Views
73
kevinc1973
K
GMC The Macro Man
  • Solved
Hide Sheet Tabs in Excel Online
Replies
3
Views
341
Fluff
Fluff
J
  • Solved
Enter multiple lines of text and auto-fit row height
Replies
6
Views
227
judgejustin
J

Share this page

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
Back
Top