Excel VBA - No cell selected

[Simon Caiger]

Well, it's one of those days,

There I was happily programming away in VB and someone comes along with an urgent Excel problem.

Without going into the detail of the whole requirement, what I need to do is if somebody clicks on a particular cell, I need to send the selected cell region elsewhere.

I have managed to select a different cell OK by using

Sheet1.Range("nm").Activate

So, for example, if the user clicks on A1 and that is one of the special cells, I pop up a message and select B1 instead.

What I would like to do is to have no cell selected if the user clicks on A1.

Is this possible?

Any help would be appreciated.

[alex_read]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If (ActiveCell.Address = "$A$1") Then
        Cells(1, 2).Select
    ElseIf (ActiveCell.Address = "$A$2") Then
        Cells(2, 2).Select
    End If
End Sub

I think Excel must have a cell selected at all times - I've never seen samples otherwise, so you're best bet would be to maybe select a cell the user isn't looking at to give that impression.

I.e. use the above code to select a cell on row 3000 for example & the user wouldn't see any cells selected on their screen then...

[BrianB]

Something like this ...........?

'--------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim NewAddress As String
    Select Case Target.Address
        Case "$A$1"
            NewAddress = "B1"
        Case "$A$2"
            NewAddress = "B2"
        Case Else
            End
    End Select
    MsgBox ("Changing selection")
    Range(NewAddress).Select
End Sub
'-------------------------------------------------

[Simon Caiger]

Thanks for the replies guys,

Sorry if I wasn't clear in my post. I had the moving to another cell bit working.

I just wanted to know if it was possible to have no cell selected after the user had clicked on one of the precious cells rather than moving the user to a cell of my choice.

Looks like the non visible cell option is the only way.

Thanks again.

[TheFIDDLER]

Only way to have no cell selected in Excel that I have found is with the following:

Protected sheet (password is optional)

and the following vba code:
Worksheets(1).EnableSelection = xlNoSelection


You could set up your selection_change event that if in a certain cell, it would lock the sheet and set the xlNoSelection property.

Just give the user the ability to unlock sheet if he/she needs to continue entering data. Once unlocked, cells can be selected again.

[Simon Caiger]

Thanks TheFIDDLER,
I will give it a try.

[zigraj]

In Range("B1") I have "TEST1" in Range("A6") I have "TEST2"
in intersection range range("B6") I have "TEST3".
How I can to obtain this value "TEST3" ?

[Simon Caiger]

Hi zigraj,

I don't know the answer to your question but I can give you some advice as to how you might get more people to see your question.

Because you have posted your question at the end of an existing thread, the only people likely to see it will be people subscribing to the thread (me and the three guys who were trying to help me) and anyone who just happens to look at the thread (not very likely).

Try starting a new thread and ask your question again.

Hope you get an answer