can a userform be 'locked' into position once displayed?

thanks

Hi,

Try this, Right click your userform, view code and paste this in. You don't
need the message box but it serves to terminate the drag instantly and stops
the userform flashing about. Try it without.

Option Explicit
Private Type position
Left As Single
Top As Single
End Type

Private Sub UserForm_Layout()
Static Pos As position
Dim Moved As Boolean
If Pos.Left = 0 Or Pos.Top = 0 Then
Pos.Left = Me.Left
Pos.Top = Me.Top
Exit Sub
End If
Moved = False
If Me.Left < Pos.Left Then
Me.Left = Pos.Left
Moved = True
End If
If Me.Top < Pos.Top Then
Me.Top = Pos.Top
Moved = True
End If
If Moved = True Then
MsgBox "Can't do that"
End If
End Sub

Mike

"Anthony" wrote:

can a userform be 'locked' into position once displayed?

thanks

Hi Mike and thanks for help

the inserted code comes back with an error and stops on this line

Static Pos As position

any ideas?

thanks

"Mike H" wrote:

Hi,

Try this, Right click your userform, view code and paste this in. You don't
need the message box but it serves to terminate the drag instantly and stops
the userform flashing about. Try it without.

Option Explicit
Private Type position
Left As Single
Top As Single
End Type

Private Sub UserForm_Layout()
Static Pos As position
Dim Moved As Boolean
If Pos.Left = 0 Or Pos.Top = 0 Then
Pos.Left = Me.Left
Pos.Top = Me.Top
Exit Sub
End If
Moved = False
If Me.Left < Pos.Left Then
Me.Left = Pos.Left
Moved = True
End If
If Me.Top < Pos.Top Then
Me.Top = Pos.Top
Moved = True
End If
If Moved = True Then
MsgBox "Can't do that"
End If
End Sub

Mike

"Anthony" wrote:

can a userform be 'locked' into position once displayed?

thanks

Hi,

Did you paste this bit in? It need all of the code if not it will give a

user defined type not defined error

Option Explicit
Private Type position
Left As Single
Top As Single
End Type

Mike

"Anthony" wrote:

Hi Mike and thanks for help

the inserted code comes back with an error and stops on this line

Static Pos As position

any ideas?

thanks

"Mike H" wrote:

Hi,

Try this, Right click your userform, view code and paste this in. You don't
need the message box but it serves to terminate the drag instantly and stops
the userform flashing about. Try it without.

Option Explicit
Private Type position
Left As Single
Top As Single
End Type

Private Sub UserForm_Layout()
Static Pos As position
Dim Moved As Boolean
If Pos.Left = 0 Or Pos.Top = 0 Then
Pos.Left = Me.Left
Pos.Top = Me.Top
Exit Sub
End If
Moved = False
If Me.Left < Pos.Left Then
Me.Left = Pos.Left
Moved = True
End If
If Me.Top < Pos.Top Then
Me.Top = Pos.Top
Moved = True
End If
If Moved = True Then
MsgBox "Can't do that"
End If
End Sub

Mike

"Anthony" wrote:

can a userform be 'locked' into position once displayed?

thanks

Hi Mike,
yep that has locked the userform, however if I drag the scroll bar accross
the worksheet the form still moves. I want it locked so that it stays in
place - almost locked to a cell range for example.

"Mike H" wrote:

Hi,

Did you paste this bit in? It need all of the code if not it will give a

user defined type not defined error

Option Explicit
Private Type position
Left As Single
Top As Single
End Type

Mike

"Anthony" wrote:

Hi Mike and thanks for help

the inserted code comes back with an error and stops on this line

Static Pos As position

any ideas?

thanks

"Mike H" wrote:

Hi,

Try this, Right click your userform, view code and paste this in. You don't
need the message box but it serves to terminate the drag instantly and stops
the userform flashing about. Try it without.

Option Explicit
Private Type position
Left As Single
Top As Single
End Type

Private Sub UserForm_Layout()
Static Pos As position
Dim Moved As Boolean
If Pos.Left = 0 Or Pos.Top = 0 Then
Pos.Left = Me.Left
Pos.Top = Me.Top
Exit Sub
End If
Moved = False
If Me.Left < Pos.Left Then
Me.Left = Pos.Left
Moved = True
End If
If Me.Top < Pos.Top Then
Me.Top = Pos.Top
Moved = True
End If
If Moved = True Then
MsgBox "Can't do that"
End If
End Sub

Mike

"Anthony" wrote:

can a userform be 'locked' into position once displayed?

thanks

Then I hope you get an answer, I don't know how to do that

Mike

"Anthony" wrote:

Hi Mike,
yep that has locked the userform, however if I drag the scroll bar accross
the worksheet the form still moves. I want it locked so that it stays in
place - almost locked to a cell range for example.

"Mike H" wrote:

Hi,

Did you paste this bit in? It need all of the code if not it will give a

user defined type not defined error

Option Explicit
Private Type position
Left As Single
Top As Single
End Type

Mike

"Anthony" wrote:

Hi Mike and thanks for help

the inserted code comes back with an error and stops on this line

Static Pos As position

any ideas?

thanks

"Mike H" wrote:

Hi,

Try this, Right click your userform, view code and paste this in. You don't
need the message box but it serves to terminate the drag instantly and stops
the userform flashing about. Try it without.

Option Explicit
Private Type position
Left As Single
Top As Single
End Type

Private Sub UserForm_Layout()
Static Pos As position
Dim Moved As Boolean
If Pos.Left = 0 Or Pos.Top = 0 Then
Pos.Left = Me.Left
Pos.Top = Me.Top
Exit Sub
End If
Moved = False
If Me.Left < Pos.Left Then
Me.Left = Pos.Left
Moved = True
End If
If Me.Top < Pos.Top Then
Me.Top = Pos.Top
Moved = True
End If
If Moved = True Then
MsgBox "Can't do that"
End If
End Sub

Mike

"Anthony" wrote:

can a userform be 'locked' into position once displayed?

thanks