How to creata a function that opens form,passes arg and closes it

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I would like to create a clean code without using many global variables and
so on and I ran into a problem.

I would like to create a function that returns an Excel.Sheet. The parametr
for the function is a workbook object that is opened previously.

I would like to open a form that loads the sheets into a listbox (the form
should be a dialog) and after the user selects one the form closes and
returns value back to the function, which returns the sheet.

When I create the form and call the function

Private Function GetSheet(W As workbook)
Dim f As Form_frmSheetChoice
Set f = New Form_frmSheetChoice
'property of the Form_frmSheerChoice that should be used on the activate event
Set f.SetWorkbook = W
f.lstSheets.SetFocus
MsgBox "pause"
End Function

I can see the form got opened but it didn't gain focus at all. If I do not
use the msgbox the function just passes by.

How can I set focus to the form ?

Any suggestions are welcome.

Thank You.
 
Hi Tomáš,

The snippet you posted suggests that you're overcomplicationg matters.
Instead of creating a new instance of frmSheetChoice, it's simpler to
use

DoCmd.OpenForm "frmSheetChoice", acNormal, , , , acDialog

Use the form's Open event for the code that populates the listbox, and
set lstSheets.TabIndex to 0 so it has focus when the form opens.

On Sun, 19 Mar 2006 12:40:28 -0800, Tomáš Pajonk <Tomáš
 
Thanks for the article. I would like to open form, pass it an object, set
focus to it, get the answer back to the function and then close the form.

Thank You for your time and help.
 
Hello,

Thank You very much for both answers. These are the easy solutions, that
will work, but not exactly how I would want.

When the form opens, it should reference the workbook and load the sheet
names. How can I do this without using global variable ?

PS: thanks for the article, I've already read it when I was searching for
this answer on the forums earlier.
 
Hi Tomáš,

Probably I'd do something like this:

1) In the calling procedure, build a string containing a delimited list
of the worksheet names, using a delimiter that is not legal in Excel
sheet names, and pass this to the form using the OpenArgs argument of
OpenForm: (AIR CODE)

Dim xlSheet As Object 'Excel.Worksheet
Dim SheetNames As String
Dim j As Long

For j = 1 to W.Worksheets.Count
SheetNames = SheetNames & W.Worksheets(j).Name & ":"
Next
SheetNames = Left(SheetNames, Len(SheetNames) - 1)

DoCmd.OpenForm "frmSheetChoice", acNormal, , , , acDialog, SheetNames


2) In the form's Open event procedure, replace the colons in Me.OpenArgs
with semicolons and Me.OpenArgs and use the result as the RowSource of
the listbox.

Me.lstSheets.RowSource = Replace(Me.OpenArgs, ":", ";")

n







If you search http://groups.google.com for

getwsnames drink soup audibly

you'll find a VBA function that returns a variant(array) containing the
names of the worksheets in a workbook. You could pass the path do
something like this in the form's Open event procedure:
 
Tomás Pajonk said:
I have one idea could I open form, fill in data and then set it as dialog ?
Click to expand...

No, but you can still use the "where" clause. And, further, you can still
pass arguments via the openargs...

And, where is the data coming from anyway?
 
Back
Top