CODEWITHSUNDEEP
excelvbashapes
Morgander
Morgander

Reputation: 89

Excel and VBA regarding shapes

I'm working on some experiments and I've run across something I'd like to do that I cannot figure out.

I would like to allow for a user to click a shape on an Excel spreadsheet that lets the user keep the shape highlighted so they can move it with the arrow keys, but when they click the shape, the VBA code fills in a field with an integer and fills the same field with a different integer if a different shape is selected by the user. The whole time keep in mind, I would like for the user to still have control of the shape on the spreadsheet.

Might anybody be able to help me figure this one out? Thank you very much.

Upvotes: 0

Views: 493

Answers (1)

Gary's Student
Gary's Student

Reputation: 96791

This is very easy to implement. There is a macro associated with the Shape. You click the Shape and the macro runs. Say the macro is like:

Sub HelloWorld()
    MsgBox "Hello World"
End Sub

We introduce a global Boolean called MoveMode

If MoveMode is False, the macro runs as usual, but if MoveMode is True, then the Shape is Selected and can be moved with the ARROW keys.

Public MoveMode As Boolean

Sub HelloWorld()
    If MoveMode Then
        ActiveSheet.Shapes(Application.Caller).Select
    Else
        MsgBox "Hello World"
    End If
End Sub

Now all we need a a little sub to change MoveMode:

Sub MAIN()
    MoveMode = Not MoveMode
End Sub

Upvotes: 1

Related Questions