list box

[FSt1]

hi all,
using windows xl, xl2003
I am developing a reminder form with a list box control where the users can
add up to ten reminders. the reminder form and code resides in the
personal.xls located in the xlstart folder. the reminders are place in range
a2:d12 of the personal.xls with a add reminder macro assigned to a custom
menu item.
index date made remind date reminder
1 07/07/07 08/08/07 doctor's appointment with Dr. Smith
a workbook open macro located in the personal.xls checks to see if the user
has added any reminders and if so, loads the reminder form. if not, the
reminder form does not load.
development is complete. time to go live.
Problem. the list box will not populate if the personal.xls is hidden. it
will populate if the personal.xls is not hidden.
queston. does anyone know how to populate a list box from a hidden file.

thanks for your time
FSt1

 

[Dave Peterson]

Any chance you're trying to select the worksheet, or select the cells to work
with them?

If yes, don't use .select. Work on the range directly.

But that's just a guess. You may want to share the code that fails.

 

[FSt1]

hi dave,
no. not using select.
here is the workbook open code in personal.xls
Private Sub Workbook_Open()

If IsEmpty(Workbooks("personal.xls").Sheets("sheet1").Range("B2")) Then
Exit Sub
Else
Load frmRR
frmRR.Show 0
AppActivate Application.Caption

End If
End Sub
here is the remove reminder form's initialization....
Private Sub frmRR_Initialize()

lb1.ColumnCount = 4
lb1.RowSource = Workbooks("personal"). _
Sheets("sheet1").Range("A212")

End Sub

Like i said, it all works as expected when personal.xls is not hidden. when
hidden, the list box in the remove reminder form will not populate.

thanks
regards
FST1

 

[Dave Peterson]

First, since the code is in the ThisWorkbook module, you don't have to rely on
the name of the workbook--you can use the Me keyword. It refers to the object
that owns the code--in this case, it's the workbook itself.

Option Explicit
Private Sub Workbook_Open()
If IsEmpty(Me.Sheets("sheet1").Range("B2")) Then
Exit Sub
Else
Load frmRR
frmRR.Show 0
AppActivate Application.Caption
End If
End Sub

Second, the name of the Initialize procedure is Userform_Initialize. It doesn't
change if you change the name of the userform (to frmRR). It's still called
Userform_Initialize.

And since the .rowsource is looking for a string, I'd use this:

Option Explicit
Private Sub UserForm_initialize()
lb1.ColumnCount = 4
lb1.RowSource = ThisWorkbook.Sheets("sheet1") _
.Range("A212").Address(external:=True)
End Sub

ThisWorkbook is the workbook that holds the code. I don't want to use the name
here, either.

ps. There are some cases where you'll want to use the name of the workbook.
It's always better to include the extension: workbooks("myworkbook.xls") is
better than workbooks("myworkbook").

Depending on how the user has some windows setting toggled, that version without
the extension could fail. I've never seen any failure if the extension was
included.

 

[FSt1]

Dave,
That did it. that was driving me nuts. I knew it had to work and i had tried
everything i knew of. I just couldn't figuare out...hidden - don't
work....unhidden...works flawlessly. Posting here was a act of absolute
desperation. i thing it was the Address(external:= true) part. I had never
run into that before.
but hey, i learned something.
Thanks again.

Regards
FSt1