Amending standard Excel popups

[Colin Hayes]

Hi

I have a couple of small queries :

Is it possible to amend the Excel popup which you get when trying to
select a protected cell? Or put another one to run in it's place? I'd
rather have one that says 'This cell cannot be changed' and leave it at
that , than the standard one which suggest digging around trying to find
the password..

Also I was wondering if the selection box in Excel (the one which shows
which cell you are selecting) could be formatted in any way. Maybe a
lighter or thinner border , or different colour...

Any help gratefully received.

Thanks.

 

[Dave Peterson]

I don't think you can change those messages. But you could stop the users from
selecting a locked cell on a protected worksheet.

Lock all the cells on the worksheet except the cell to edit--keep that unlocked.

(Format|Cells|Protection tab is where you do that work)

Then add a macro that protects the worksheet (nicely) when the workbook opens:

Private Sub Workbook_Open()
With Worksheets("Sheet99999")
.Unprotect Password:="hi"
.EnableSelection = xlUnlockedCells
.Protect Password:="hi", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End With
End Sub

In fact, in xl2k and below, this setting wasn't remembered when you closed the
workbook and reopened. Setting it in code was the only way to do this.

 

[Gord Dibben]

Colin

Excel's standard messages cannot be altered.

For the cell selection you could use event code to color the cell that you
select.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldCell As Range
If Application.CutCopyMode = 0 Then
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
OldCell.Borders.LineStyle = xlLineStyleNone
End If
Set OldCell = Target
OldCell.Interior.ColorIndex = 6
OldCell.Borders.LineStyle = xlContinuous
Else
If OldCell Is Nothing Then
Set OldCell = Target
Else
Set OldCell = Union(OldCell, Target)
End If
End If
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that module.

If you want for all sheets and all workbooks, best to go with Chip Pearson's
RowLiner add-in.

http://www.cpearson.com/excel/RowLiner.htm


Gord Dibben MS Excel MVP

 

[Colin Hayes]

HI

OK Thanks for that. I was hoping that if I couldn't change the popup , I
could maybe call my own message box in it's place by way of macro
perhaps.

The code for the selection box actually colours the cell it's focussed
on , which I can would be useful in some circumstances. In my
application , I was just trying to change the colour of the border , or
make the border thinner , so it would not quite so prominent. I did
implement the code you kindly offered , but it got very complicated due
to the set up of my worksheet.

Anyway , I'm grateful for your advice.

Next project is how to suppress the DOS box called by a .bat file run
from Excel....

Best Wishes


Colin

 

[Gord Dibben]

Sub backitall()
Dim taskID As Variant
On Error Resume Next
Shell ("C:\copyback.bat"), vbHide
End Sub


Gord