Locking Drawing objects on screen

[The Tamer]

Hi all,

I'm aware of how to change a drawing object's setting so that the object doesn't resize with cells etc. but is there any code that will lock an object's horizontal position even when the sheet is scrolled to the left or right?

Regards

Damo

[Killian]

Is it not the case that

<object>.Placement = xlFreeFloating

means the object doesn't move or size with cells?

[The Tamer]

Hi Killian

I just tried that, but the object still disappears from view when you scroll the sheet...

[Killian]

Oh, now I understand... you want the object float over the sheet and stay fixed relative to the view...

I've no idea how that could work. There's no event fired when the user scrolls (as far as I know) which makes it tricky.

Depending on what you're doing you could split the window so the part with the object doesn't scroll. Other than that, the only thing I can think of that would sit above the worksheet while it's scrolled around in a modeless form

[The Tamer]

Thanks anyway Killian.



I guess Excel can't do everything!

[Jacob Hilderbrand]

I guess Excel can't do everything!

blasphemy!! That's 1000 lashes for you.

Create a User Form and add this code.

Option Explicit 
 
Private Sub UserForm_Initialize()
Call RemoveCaption(Me)
End Sub

Insert a module and add this code.

Option Explicit

Private Declare Function FindWindow Lib "User32" _
Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function GetWindowLong Lib "User32" _
Alias "GetWindowLongA" ( _
ByVal hwnd As Long, _
ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "User32" _
Alias "SetWindowLongA" (ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
Private Declare Function DrawMenuBar Lib "User32" ( _
ByVal hwnd As Long) As Long

Sub RemoveCaption(objForm As Object)
Dim lStyle          As Long
Dim hMenu           As Long
Dim mhWndForm       As Long
If Val(Application.Version) < 9 Then
        mhWndForm = FindWindow("ThunderXFrame", objForm.Caption)    'XL97
    Else
        mhWndForm = FindWindow("ThunderDFrame", objForm.Caption)    'XL2000+
    End If
    lStyle = GetWindowLong(mhWndForm, -16)
    lStyle = lStyle And Not &HC00000
    SetWindowLong mhWndForm, -16, lStyle
    DrawMenuBar mhWndForm
End Sub

Sub ShowForm()
UserForm1.Show False
End Sub

Add whatever images you want to the User Form.

See the attachment for an example.

[The Tamer]

Hey Jake - thanks for helping me with this.

It's probably an XL97 thing, but I'm getting a "Compile error: Wrong number of arguments or invalid property assignment" on the line "UserForm1.Show False", the .show is highlighted.

Any Ideas mate?

[Jacob Hilderbrand]

Oh... Excel 97 you say...

Well Excel 97 doesn't really support Modeless User Forms.

[Jacob Hilderbrand]

Note that you can "trick" Excel 97 into making a User Form go Modeless. But this is only a glitch and I cannot guarantee that it won't crash Excel intermittently.

Option Explicit
 
Private Declare Function FindWindowA Lib "user32" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function EnableWindow Lib "user32" ( _
ByVal hWnd As Long, _
ByVal bEnable As Long) As Long
 
Private Sub UserForm_Activate()
Dim mlHWnd As Long
mlHWnd = FindWindowA("XLMAIN", Application.Caption)
EnableWindow mlHWnd, 1
Application.CellDragAndDrop = False
End Sub

[The Tamer]

Where do I paste this code Jake?

(Sorry, I'm thick, remember?)

[Jacob Hilderbrand]

It goes in the User Form code section. Just double click on the User Form and put in the the Code Window that opens up.

Try the attachment and see if it goes Modeless.

[The Tamer]

Nope, your attachment is still failing at ".show".

[Jacob Hilderbrand]

I forgot to take out the Modal argument.

Change this:

UserForm1.Show False

To this:

UserForm1.Show

[The Tamer]

Hey, that works!

Blast!! I AM a blasphemer!! Damn and double-damn!!!

Cheers mate

[Jacob Hilderbrand]

Glad to help.

Take Care