Worksheet_SelectionChange event not firing !?!

[Batbloke]

Hi all,
Worksheet_SelectionChange event doesn't fire if I change the selection from a cell to an image.
Does it only fire if a cell is selected (it seems to be the case, but I thought I'd check)?

Option Explicit

Private Sub Worksheet_Activate()
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveCell.Offset(0, 0).Activate
End Sub

If it is the case it's only for cells, then any ideas on how to dynamically code for an image being selected?
A macro on each image wouldn't work in my case.

Thanks

 

[6StringJazzer]

The Selection event is only for cells.

A macro on each image is the solution, but it can be the same macro. Why will this not work in your case?

 

[Batbloke]

Hi,
Each image is laid dynamically by the program onto cells exactly the same size. I have no way (that I know of) to assign the macro to each image (or shape, to be more precise).
The problem is that while I can turn off selection of shapes as they are laid, I can't find a way to turn off the selection (in code) when a user clicks on a shape.
I seem to have hit a dead end.

 

[6StringJazzer]

In the code where you create the shape you can also assign a macro. Your macro can use things like Application.Caller to figure out which shape was clicked.

Dim shpTemp As Shape
Set shpTemp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, 1680#, 594#, 120#, 74.25)
shpTemp.Onaction = "'" & activeworkbook.name & "'!macro_name"

I can't find a way to turn off the selection (in code) when a user clicks on a shape.

I can't figure out what you mean by this. I thought you wanted to turn on selection in code when a user clicks a shape.

Hard to give much more detail, without more detail about what you are doing.

 

[Batbloke]

Hi Jeff,
I'll try and give a brief on what is happening -
I have a form which allows the user to select a "terrain" (an image).
The user then selects an area within a bounded region on a sheet x1,y1 - xn,yn
The user then clicks on a button and the selected range fills with the terrain (each exactly the pre-set, fixed size of the cells beneath.
Using ActiveCell.Offset(0, 0).Activate (see the code below), all laid images are deselected
The problem is
a/ I need to allow the user to have "mouse access" to the sheet, in order to select an area (may just be one cell)
b/ I don't want them to be able to select an image (with all its grab handles, etc to move/resize,/whatever)
or at least for my code to be able to immediately switch the selection off.
I've tried lots of things, and done research but I can find nothing to get around it. I've been stuck at this bit for 3 days + now
so any help would be greatly appreciated.

Here's a sample of my prototype image handling code - all this works well, with no issues.

Public Sub UTPutTerrainInCell(piX As Integer, piY As Integer, psTerrainName As String, pbOverwrite As Boolean)

'pbOverwrite forces the current cell image and record to be deleted and overwritten, ...

    Dim loRS As Recordset
    Dim lsFilename As String
    Dim lsFullPath As String
    Dim loPicInCell As Picture
   
    'Get the filename of the terrain image to be laid
    Set loRS = goDb.OpenRecordset("SELECT * FROM tbTerrainLibrary WHERE Terrain = '" & _
    psTerrainName & "'")
   
    With loRS
        lsFilename = .Fields("Filename")
        .Close
    End With
    Set loRS = Nothing
    'If the cell already holds an image
    If UTGetCellTerrain(piX, piY) <> "Empty" Then   'Delete image and record...
        If pbOverwrite Then                         '... but only if chkOverlay is set
            mbCancel = False
            UTDeleteTerrainImageAndItsLayRecord piX, piY
            If mbCancel Then
                mbCancel = False
                Exit Sub
            End If
        Else
           
            MsgBox "Terrain already at this location" & vbCrLf & _
            "If you want to change it, select Overlay" & vbCrLf & "and try again", _
            vbInformation, "Space Occupied"
           
            Exit Sub
        End If
    End If
    glImageCount = glImageCount + 1
    lsFullPath = gsTerrainImagePath & lsFilename
    Set loPicInCell = goXsTT.Pictures.Insert(lsFullPath)
    loPicInCell.Name = Trim(CStr(glImageCount))
    goXsTT.Shapes.Range(Array(glImageCount)).Select
    goXsTT.Shapes.Range(Array(Trim(CStr(glImageCount)))).Name = Trim(CStr(glImageCount))
    ActiveCell.Offset(0, 0).Activate
    loPicInCell.SendToBack
    Set loRS = goDb.OpenRecordset("tbLay")
    With loRS
        .AddNew
        .Fields("ID").Value = glImageCount
        .Fields("CellAddress") = loPicInCell.TopLeftCell.Address(False, False)
        .Fields("Col").Value = piX
        .Fields("Row").Value = piY
        .Fields("Terrain").Value = psTerrainName
        .Update
        .Close
    End With
    Set loRS = Nothing
End Sub

Thanks, Dave

 

[Batbloke]

Okay, I have decided to use a workaround .
When it comes to select the range by mouse for the "laying" of the images, I shall - as usual, unprotect the sheet, but before I do I make each previously laid image invisible
and hence not selectable.
Now, for the user to know their is a "terrain" at certain locations, the cell will contain the name of the terrain type (e,g "Woodland"), it will also be coloured a pre-chosen colour for each terrain type. (There potentially will be hundreds of terrain types, but having some colouring will help, differentiate types.
As soon as the user clicks to lay the new terrain, the sheet will be protected and all the terrain images made visible.

Not the most elegant of solutions - but it is a solution