Hello --

Is it possible to do this?

Dave Peterson suggested that Screenupdating should be turned on when you
showing the form.
This does not solve the problem.

Ivan Moala suggested that code selecting a Hidden sheet might be the
problem. My code does not include that.

Thanks for any help.

Larry Mehl

Larry,

Following will create a non movable userform.

Option Explicit

Private Const MF_BYCOMMAND As Long = &H0&
Private Const SC_MOVE As Long = &HF010
Private Const SC_SEPARATOR As Long = &HF00F

'USER32
Private Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function GetSystemMenu Lib "user32" ( _
ByVal hwnd As Long, ByVal bRevert As Long) As Long
Private Declare Function DrawMenuBar Lib "user32" ( _
ByVal hwnd As Long) As Long
Private Declare Function RemoveMenu Lib "user32" ( _
ByVal hMenu As Long, ByVal nPosition As Long, _
ByVal wFlags As Long) As Long


Private Sub UserForm_Activate()
lockPos
End Sub

Sub lockPos()
Dim hWndForm As Long, hSysMenu As Long, nCnt As Long

hWndForm = FindWindow(vbNullString, Me.Caption)
If hWndForm Then
hSysMenu = GetSystemMenu(hWndForm, False)

If hSysMenu Then
RemoveMenu hSysMenu, SC_MOVE, MF_BYCOMMAND
RemoveMenu hSysMenu, SC_SEPARATOR, MF_BYCOMMAND
DrawMenuBar hWndForm
End If
End If

End Sub





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"L Mehl" wrote:

Hello --

Is it possible to do this?

Dave Peterson suggested that Screenupdating should be turned on when
you showing the form.
This does not solve the problem.

Ivan Moala suggested that code selecting a Hidden sheet might be the
problem. My code does not include that.

Thanks for any help.

Larry Mehl

keepITcool --

Thank you very much for the code. It works ... the form is now
non-moveable.

I need to change 2 behaviors of the form. The behaviors appeared after
including the code.

1
The form is shown by clicking on a button in the "calling" book1/sheet1.
The form's UserForm_Activate event starts a process which involves a process
in book2/sheet2 (filtering a table of values to appear in the form's
ListBox). I do not want book2/sheet to be visible to the user during this
process, so I use
Application.ScreenUpdating = False
(this technique prevented appearance of book2/sheet2 before I added your
code to the app).

2
When I click the form's caption bar and move the cursor away from the
caption bar, to a position over book2/sheet2 underlying the form, after a
few seconds, the form closes and I am returned to the book/sheet on which I
clicked the button to show the form.

Never knowing what crazy thing the user will do, I want the form to not
close if this happens.

Do you have any ideas of how to solve the problems in 1 and 2 above?

Thanks for any additional help.

Larry Mehl

"keepITcool" wrote in message
...
Larry,

Following will create a non movable userform.

Option Explicit

Private Const MF_BYCOMMAND As Long = &H0&
Private Const SC_MOVE As Long = &HF010
Private Const SC_SEPARATOR As Long = &HF00F

'USER32
Private Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function GetSystemMenu Lib "user32" ( _
ByVal hwnd As Long, ByVal bRevert As Long) As Long
Private Declare Function DrawMenuBar Lib "user32" ( _
ByVal hwnd As Long) As Long
Private Declare Function RemoveMenu Lib "user32" ( _
ByVal hMenu As Long, ByVal nPosition As Long, _
ByVal wFlags As Long) As Long


Private Sub UserForm_Activate()
lockPos
End Sub

Sub lockPos()
Dim hWndForm As Long, hSysMenu As Long, nCnt As Long

hWndForm = FindWindow(vbNullString, Me.Caption)
If hWndForm Then
hSysMenu = GetSystemMenu(hWndForm, False)

If hSysMenu Then
RemoveMenu hSysMenu, SC_MOVE, MF_BYCOMMAND
RemoveMenu hSysMenu, SC_SEPARATOR, MF_BYCOMMAND
DrawMenuBar hWndForm
End If
End If

End Sub





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"L Mehl" wrote:

Hello --

Is it possible to do this?

Dave Peterson suggested that Screenupdating should be turned on when
you showing the form.
This does not solve the problem.

Ivan Moala suggested that code selecting a Hidden sheet might be the
problem. My code does not include that.

Thanks for any help.

Larry Mehl

1..
if you code properly you can filter all you want without the sheet
ever being activated.
Of course the words .SELECT and .ACTIVATE and SELECTION
are to be avoided at all cost.

2...
try putting my code as the first line in the activate
event. (you cant find the window handle if you put it
Initialize)

is your modeless or modal?

i dont see how it should close automatically...
probably some other events triggering that.

what the heck

<LOL

zip and mail. I'll have a look..probably tomorrow :)





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"L Mehl" wrote:

keepITcool --

Thank you very much for the code. It works ... the form is now
non-moveable.

I need to change 2 behaviors of the form. The behaviors appeared
after including the code.

1
The form is shown by clicking on a button in the "calling"
book1/sheet1. The form's UserForm_Activate event starts a process
which involves a process in book2/sheet2 (filtering a table of values
to appear in the form's ListBox). I do not want book2/sheet to be
visible to the user during this process, so I use
Application.ScreenUpdating = False
(this technique prevented appearance of book2/sheet2 before I added
your code to the app).

2
When I click the form's caption bar and move the cursor away from the
caption bar, to a position over book2/sheet2 underlying the form,
after a few seconds, the form closes and I am returned to the
book/sheet on which I clicked the button to show the form.

Never knowing what crazy thing the user will do, I want the form to
not close if this happens.

Do you have any ideas of how to solve the problems in 1 and 2 above?

Thanks for any additional help.

Larry Mehl

Glad that keepitcool could give you the answer.

But I was answering how to keep the "cascading" effect from happening when you
moved the userform--not preventing the form from moving.

L Mehl wrote:

Hello --

Is it possible to do this?

Dave Peterson suggested that Screenupdating should be turned on when you
showing the form.
This does not solve the problem.

Ivan Moala suggested that code selecting a Hidden sheet might be the
problem. My code does not include that.

Thanks for any help.

Larry Mehl

--

Dave Peterson

K -

Thanks for your offer to look at my stuff.

Can you help with an example of proper coding to avoid
1 - the sheet being activated
2 - words .SELECT and .ACTIVATE and SELECTION
(I am guilty of using all 3 bad words in my code to filter)

My goal is to populate book A/sheet A's form's listbox with a list of values
from book B/sheet B,
so user can select a value from the listboxt

book A/sheet A contains a cell to be populated with a selection from
database to be filtered
book B/sheet B contains the database to be filtered
(code places filtered values in vertical range of cells: strRangeExtract)
(range strRangeExtractTop is cell above this range)

User clicks btn on Sheet A

Btn does:
1 - writes value related to btn in a cell on sheet B
2 - shows form for selecting a value from lstSelectComponent:
frmSelectComponent.Show

frmSelectComponent's UserForm_Activate runs code to filter and place list of
items in sheet B's strRangeExtract

Private Sub UserForm_Activate()

Dim strCellEqpSelDisplay As String 'display value of selected eqp type
Dim strCellRngDest As String 'cell containing value of selected eqp
type
Dim intRegionCode As Integer

On Error GoTo UserForm_Activate_Error

'get the component type from Sheet A
g_strCellEqpSel = Cells.Range("eqp_sel").Value
'get region code from Sheet A
intRegionCode = Cells.Range("rgn_code_eng_lkup").Value

'run AdvancedFilter code to populate Sheet B range as source for
' Sheet A's form's listbox
'parameters pass generic equipment type and region code
Call FilterComponent_Rgn(g_strCellEqpSel, intRegionCode)

Me.lstSelectComponent.RowSource = "result_prod_rgn_nonnull"

End Sub


Sub FilterComponent_Rgn(ProdType As String, RegionCode As Integer)
Windows(strBookSource).Activate '[bad word]
ActiveWorkbook.Worksheets(strSheetSource).Activate

'populate ProductType and RegionCode cells in Sheet B
Cells.Range(strRangeCriteriaProdType).Value = ProdType
Cells.Range(strRangeCriteriaRgnCode).Value = RegionCode

Range(strRangeExtract).Select '[bad word]
Selection.ClearContents

Range(strRangeData).AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range(strRangeCriteria), _
CopyToRange:=Range(strRangeExtractTop), _
Unique:=True

'... code to strip blank values in Sheet B's extract range and
' put non-null values into Sheet B's range "result_prod_rgn_nonnull"

End Sub
----------------------

Form is modal.

Thanks for any help.

Larry


"keepITcool" wrote in message
...
1..
if you code properly you can filter all you want without the sheet
ever being activated.
Of course the words .SELECT and .ACTIVATE and SELECTION
are to be avoided at all cost.

2...
try putting my code as the first line in the activate
event. (you cant find the window handle if you put it
Initialize)

is your modeless or modal?

i dont see how it should close automatically...
probably some other events triggering that.

what the heck

<LOL

zip and mail. I'll have a look..probably tomorrow :)





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"L Mehl" wrote:

keepITcool --

Thank you very much for the code. It works ... the form is now
non-moveable.

I need to change 2 behaviors of the form. The behaviors appeared
after including the code.

1
The form is shown by clicking on a button in the "calling"
book1/sheet1. The form's UserForm_Activate event starts a process
which involves a process in book2/sheet2 (filtering a table of values
to appear in the form's ListBox). I do not want book2/sheet to be
visible to the user during this process, so I use
Application.ScreenUpdating = False
(this technique prevented appearance of book2/sheet2 before I added
your code to the app).

2
When I click the form's caption bar and move the cursor away from the
caption bar, to a position over book2/sheet2 underlying the form,
after a few seconds, the form closes and I am returned to the
book/sheet on which I clicked the button to show the form.

Never knowing what crazy thing the user will do, I want the form to
not close if this happens.

Do you have any ideas of how to solve the problems in 1 and 2 above?

Thanks for any additional help.

Larry Mehl

Yes, glad Keepitcool gave you a solution, but as Dave pointed out th
answer we supplied was for the cascading effect. I think that by no
being able to move the form you have put a band aid fix on th
underlying problem. ie stop the cascading effect. But if you are happ
with the results then go for it.

Dave Peterson Wrote:
Glad that keepitcool could give you the answer.

But I was answering how to keep the "cascading" effect from happenin
when you
moved the userform--not preventing the form from moving.

L Mehl wrote:

Hello --

Is it possible to do this?

Dave Peterson suggested that Screenupdating should be turned on whe
you
showing the form.
This does not solve the problem.

Ivan Moala suggested that code selecting a Hidden sheet might be the
problem. My code does not include that.

Thanks for any help.

Larry Mehl

--

Dave Peterson


--
Ivan F Moal

-----------------------------------------------------------------------
Ivan F Moala's Profile: http://www.excelforum.com/member.php...nfo&userid=195
View this thread: http://www.excelforum.com/showthread.php?threadid=27037

Larry,

a quick scan of your code..



.....


'Note: the form is currently shown..
'IF you have already SET the rowsource of SelectComponent THEN
'you'll be working and clearing in the rowsource of the control....


'That is slow.. may block cells, and trigger events on the form.
'So before working on a rowsource range be sure to "FREE" the range.


Me.lstSelectComponent.RowSource = vbNullString
Call FilterComponent_Rgn(g_strCellEqpSel, intRegionCode)
Me.lstSelectComponent.RowSource = _
ThisWorkbook.Range("result_prod_rgn_nonnull").Addr ess(external:
=True)



End Sub


Sub FilterComponent_List(ProdType As String, RegionCode As Integer)

'AVOID THE USE OF WINDOWS.. USE WORKBOOKS INSTEAD
'Windows(strBookSource) will not work if you have 2 windows for that
book open.

'you could set a worksheet variable..
'Dim ws As Worksheet
'Set ws = Workbooks(strBookSource).Worksheets(strSheetSource )
'then EITHER use
'ws.Range

'or With/end with

'With ws

'OR you could do it in 1 line..


With Workbooks(strBookSource).Worksheets(strSheetSource )

'within a WITH you need the DOT to "tunnel"
'populate ProductType and RegionCode cells in Sheet B

.Range(strRangeCriteriaProdType).Value = ProdType
.Range(strRangeCriteriaRgnCode).Value = RegionCode

.Range(strRangeExtract).ClearContents

.Range(strRangeData).AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range(strRangeCriteria), _
CopyToRange:=Range(strRangeExtractTop), _
Unique:=True

'... code to strip blank values in Sheet B's extract range and
' put non-null values into Sheet B's range
"result_prod_rgn_nonnull"

End With


End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"L Mehl" wrote:

Me.lstSelectComponent.RowSource = "result_prod_rgn_nonnull"

K --

Thanks for your comments and for introducing me to these concepts. It will
take a while to digest it.

I'll probably be back.

Larry


"keepITcool" wrote in message
...
Larry,

a quick scan of your code..



....


'Note: the form is currently shown..
'IF you have already SET the rowsource of SelectComponent THEN
'you'll be working and clearing in the rowsource of the control....


'That is slow.. may block cells, and trigger events on the form.
'So before working on a rowsource range be sure to "FREE" the range.


Me.lstSelectComponent.RowSource = vbNullString
Call FilterComponent_Rgn(g_strCellEqpSel, intRegionCode)
Me.lstSelectComponent.RowSource = _
ThisWorkbook.Range("result_prod_rgn_nonnull").Addr ess(external:
=True)



End Sub


Sub FilterComponent_List(ProdType As String, RegionCode As Integer)

'AVOID THE USE OF WINDOWS.. USE WORKBOOKS INSTEAD
'Windows(strBookSource) will not work if you have 2 windows for that
book open.

'you could set a worksheet variable..
'Dim ws As Worksheet
'Set ws = Workbooks(strBookSource).Worksheets(strSheetSource )
'then EITHER use
'ws.Range

'or With/end with

'With ws

'OR you could do it in 1 line..


With Workbooks(strBookSource).Worksheets(strSheetSource )

'within a WITH you need the DOT to "tunnel"
'populate ProductType and RegionCode cells in Sheet B

.Range(strRangeCriteriaProdType).Value = ProdType
.Range(strRangeCriteriaRgnCode).Value = RegionCode

.Range(strRangeExtract).ClearContents

.Range(strRangeData).AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range(strRangeCriteria), _
CopyToRange:=Range(strRangeExtractTop), _
Unique:=True

'... code to strip blank values in Sheet B's extract range and
' put non-null values into Sheet B's range
"result_prod_rgn_nonnull"

End With


End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"L Mehl" wrote:

Me.lstSelectComponent.RowSource = "result_prod_rgn_nonnull"