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