How to warn users that multiple sheets are selected before data entry

[petrosky]

Hi,

As the name suggests, I have some users here who occasionally are guilty of this transgression.

Any ideas on how to test for this condition?

Thanks in advance.

Peter Remember- It's nice to be important,
but it's important to be nice

 

[SkipVought]

Hi,

This happened to me just yesterday -- aaaaaagghhh!

But alas, there is no way to remind the user that...

they have multiple sheets/cells or a host of other conditons amongst which lurk a myriad of menaces. ;-) Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

It's a bit of a kludge, and maybe the cure is worse than the disease, but with VBA you could do something like this:
Put this macro in each worksheet:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  CheckMultiSheets Target
End Sub

Then put this code in a module:

Option Explicit

Dim LastSheet As String
Dim LastAddress As String

Sub CheckMultiSheets(ByVal Target As Excel.Range)
  With Target
    If .Worksheet.Name <> LastSheet Then
      If .Address = LastAddress Then
        MsgBox &quot;Multisheet selection&quot;
      End If
    End If
    LastSheet = .Worksheet.Name
    LastAddress = .Address
  End With
End Sub

If multisheet updates are sometimes really wanted, then the code could be bracketed by an IF( ) statement, testing another global variable to allow the action to be effectively switched off.

 

[petrosky]

Hi Zathras/Skipvought,

Thanks for the input.

I have muddled around with your solution Zathras and I think you are right when you say that the cure maybe worse than the disease.

I will post back if I find a "clean" way to handle this situation.

Thanks again.

Peter Remember- It's nice to be important,
but it's important to be nice

 

[nosugar]

Tha vast majority of workbooks use just one worksheet. I would change the default worksheet count to one from three. If the user needs mor than one worksheet they will have to have the knowledge to select more than one worksheet. Hopefully they will deselect after the command or change. The due diligence will come when checking the data.

As I am typing this an idea came to me. Why not have a macro running in the background polling each sheet. If a change occurs and the focus is not on the sheet the pc will beep or some other subtle attention getting device.

Hope this may help.

David