Excel VB ListBox 'Height' Problem

[tommo_blade]

Hi, I have some code in my Excel spreadsheet that pops up a listbox
when a certain cell is selected, the contents of that listbox are
pulled from a hidden sheet within the excel workbook. My problem is in
getting the last row of data visible in the list box, it is just
visible at the bottom of the listbox but the vertical scroll will not
scroll down any further, if I go into the code and adjust the 'height'
property then go back to the screadsheet and select the cell I can see
the last row of data ok, when I save the changes, close down excel &
re-open again the problem has re-occurred, here are the listbox
properties I am using:-

MidfieldersListBox.Visible = True
MidfieldersListBox.Left = 354
MidfieldersListBox.Width = 531
MidfieldersListBox.Height = 300
MidfieldersListBox.Top = 10

I seem to be able to get the last row of data visible by altering
either the width or height property but as described above it is lost
when I close/open the spreadsheet - is this a know issue and is there
a workaround.

thanks in advance, Mark.

 

[Office_Novice]

There is indeed.. How ever it is not done easily. Have a look here and post
back with questions.

http://www.cpearson.com/excel/formcontrol.aspx

 

[tommo_blade]

Ok I understand a little about accessing windows objects but I am
struggling to visualise how this will work with my problem, what I
think you are saying is this:-

In my present solution the listbox's are hidden until certain cells
are selected, at this point the listbox becomes visible and in that
listbox are the contents of a 'hidden worksheet' with the last row in
the listbox not being visible.

For your suggestion, when the cell is selected instead of the listbox
being presented the windows userform will be called (which can be
tweaked to any size) and this will display the contents of the hidden
worksheet.

One Question: what is the exact problem with the listbox, I can alter
the 'height' property of the box and it displays the whole data ok, it
is only after I close the spreadsheet and re-open that the problem
occurrs.

cheers.

 

[Office_Novice]

Your Right i did misunderstand the problem. Try not making the Listbox
visible until after you have adjusted the size properties.