How do I fill a List or ComboBox with a range showing no blank spaces

[freezer]

Hello People,

I am new to the world of vba and am trying to get a list or combobox to display a variable range of information from say A2 to end. The problem is that there are blank cells in column A between those I need for listBox and I Don't want Them, just those cells with a value. Also complicating the project are nine combobuttons that determin where the single list box gets its info from but I may have that one covered if I can clear up the blanks shown in the listbox.

Being a newbe I don't know the ins and outs of the dims and sets yet so this is likely where it is.

thanks
Freezer

 

[msmith]

Freezer,

This should get you started. Change object names as appropriate.

Sub FillList_NoBlanks()
Dim LastUsedRow As Long
Dim Rng As Range
Dim OneCell As Range

  With Worksheets("Sheet1")
    LastUsedRow = .Cells(65536, 1).End(xlUp).Row
    Set Rng = .Range(.Cells(2, 1), .Cells(LastUsedRow, 1))
    For Each OneCell In Rng
      If Not IsEmpty(OneCell.Value) Then
        UserForm1.ListBox1.AddItem OneCell.Text
      End If
    Next OneCell
  End With
  
End Sub

Call this procedure from Userform1's Initialize event procedure.

HTH
Mike

 

[freezer]

Mike,
That is just what I was looking for, now if I can figure out the Dims and outs as needed I will be in great shape.

I have the Excel 2002 programing book but at times it's a little vague or short in explanation.

Again
Many Thanks

 

[msmith]

Freezer,

Not sure which book you have but I recommend "Excel xxxx Power Programming With VBA" by John Walkenbach where xxxx represents 2000 and 2002 (I think there was a 95 edition as well). Topics are explained very well with plenty of examples (all workbooks/VBA code available on the enclosed CD). I personally use the Excel 2000 edition (which also covers Excel 97); it does not gather dust.


Regards,
Mike

 

[freezer]

Mike,

One last small bit is that each of the "9" option buttons on the form activate a different sheet to bring a list up in the same listbox while the userform is still showing.
Can I use the refresh command at button click to bring the new list in from the new sheet??

mike

 

[freezer]

Mike,

I got it.
I added
Set Sheet = Application.ActiveSheet
changed your (with) to
with sheet
added
UserForm1.ListBox1.Clear
just before calling for FillList_NoBlanks
in button (Click)

I Hope It's right, it works

Thanks again

 

[msmith]

Hey, if it works, it ain't wrong!

I'll throw out something from an application design standpoint. Typically, if you are displaying a list in a listbox or combobox on a form, you don't want or need for the user to see the source listing on the worksheet. I would put all 9 (?) lists on a single worksheet, reference them in my code as part of the Click procedure of the option buttons, and hide the worksheet. Something to think about.


Regards,
Mike

 

[freezer]

Mike,

That would not work as each sheet contains a seperate listing of information that is not related to the other sheets. All information in each sheet is expanding based on individual input from multiple user forms and the expansion is at different rates.

I do however need to hide these sheets and all others except one for display. After I got that little listbox problem working (Thanks to you again) I found that I can't hide a sheet and select or activate it. You mention hiding the sheet, at your suggestion I now have Excel VBA programing for 2002 by John Walkenbach but have found no way to hide these sheets and still access them except for a small mention about bubble sort and being trasparant

If I can't hide these sheets it destroys the whole idea of simple acess through user forms. Sorry to be so long winded but can you still help?
Is there a way to hide and still select that I can code at the start or a different way to reference those sheets and cells and I will re-write based on this?

Thanks
Mike N.

 

[msmith]

Mike,

You are quite correct in that you cannot select objects on a hidden worksheet (including selecting the worksheet itself). However, the good news is that you rarely need to in order to work with those objects. Your code can reference the object directly (or through an assigned variable) then change properties, invoke methods, etc. If you look at Walkenbach's code samples you will not see constructs such as

Range("A1").Select
Selection.Value = 10

but rather:

Range("A1").Value = 10

In a similar fashion, you don't need to do something like

Worksheets("Sheet1").Activate
ActiveSheet.StandardWidth = 15

Reference the worksheet directly:

Worksheets("Sheet1").StandardWidth = 15

Can you post a specific code sample that fails with an error?


Regards,
Mike

 

[freezer]

Put this in the gen declarations of a user form (UserFrom1)
Add the list box and two OptionButtons and put a little value into several cells in column A of the two sheets.

Private Sub OptionButton1_Click()
Sheets("sheet1&quot.Select
UserForm1.ListBox1.Clear
Call UserForm1.FillList_NoBlanks
End Sub

Private Sub OptionButton2_Click()
Sheets("sheet2&quot.Select
UserForm1.ListBox1.Clear
Call UserForm1.FillList_NoBlanks
End Sub


Private Sub UserForm_Initialize()
Call UserForm1.FillList_NoBlanks
End Sub
Sub FillList_NoBlanks()


Dim LastUsedRow As Long
Dim Rng As Range
Dim OneCell As Range
Dim sheet As Variant
Set sheet = Application.activesheet
With sheet
LastUsedRow = .Cells(65536, 1).End(xlUp).Row
Set Rng = .Range(.Cells(2, 1), .Cells(LastUsedRow, 1))
For Each OneCell In Rng
If Not IsEmpty(OneCell.Value) Then
UserForm1.ListBox1.AddItem OneCell.Text
End If
Next OneCell
End With

End Sub

 

[msmith]

Mike,

Yes, the problem is your code is attempting to select the hidden sheets "Sheet1" & "Sheet2" which causes an error. I've modified your code to avoid selecting worksheets:

Private Sub OptionButton1_Click()
  UserForm1.ListBox1.Clear
  Call UserForm1.FillList_NoBlanks(ThisWorkbook.Worksheets("Sheet1"))
End Sub


Private Sub OptionButton2_Click()
  UserForm1.ListBox1.Clear
  Call UserForm1.FillList_NoBlanks(ThisWorkbook.Worksheets("Sheet2"))
End Sub


Private Sub UserForm_Initialize()
  Me.OptionButton1.Value = True
End Sub


Sub FillList_NoBlanks(ByVal Wks As Worksheet)
Dim LastUsedRow As Long
Dim Rng As Range
Dim OneCell As Range

  With Wks
    LastUsedRow = .Cells(65536, 1).End(xlUp).Row
    Set Rng = .Range(.Cells(2, 1), .Cells(LastUsedRow, 1))
    For Each OneCell In Rng
      If Not IsEmpty(OneCell.Value) Then
        UserForm1.ListBox1.AddItem OneCell.Text
      End If
    Next OneCell
  End With
  
End Sub

Try these changes and post back with your result.


Regards,
Mike

 

[freezer]

Mike,
It works great. I also found out over the weekend that if I change the .Select at the end of the Sheet line to .Activate I get the same results. It took a while to adjust the rest of the program but I now have 11 sheets hidden behind my main cover sheet and all accessable and sharing info. I hope that is also a good way to get from point A to point B, I pulled it from that new book.

Any opinion?

Many Thanks again,
Mike N.

 

[RTyndall]

I have tried to call the no space in list code and got a Permission denied error. I can not seem to be able to figure out why. Please Help. I have included the modified code and the form initilization below.

Private Sub FillStrNames()
Dim LastUsedRow As Long
Dim Rng As Range
Dim OneCell As Range

With Worksheets("Data&quot
LastUsedRow = .Cells(65536, 3).End(xlUp).Row
Set Rng = .Range(.Cells(2, 3), .Cells(LastUsedRow, 3))
For Each OneCell In Rng
If Not IsEmpty(OneCell.Value) Then
fmMain.cbStrName.AddItem OneCell.Text
End If
Next OneCell
End With

End Sub


Private Sub UserForm_Initialize()
Call FillStrNames
With Range("Data!Database&quot
Set rgStrData = .Rows(2)
Set rgPipeData = .Rows(3)

Call LoadRecord
sbNav.Value = 2
sbNav.Max = .Rows.Count
End With
Call FillStrNames
End Sub

Thanks,
Rich

 

[msmith]

Rich,

You don't say for certain which line is causing the error. Also, I have to assume you are referring to run-time error number 70, "Permission Denied". If so, it is puzzling because that error is caused by an illegal write operation to a disk file or the registry. I don't see anything in your code that would cause that; certainly not a call to the IsEmpty function. Can you shed any additional light?


Regards,
Mike

 

[freezer]

RTyndall:

I have a copy of a program that I use for testing small applications like this that I can send you. The ability to select one of the items from the list along with the list itself has been modified to include a temp. array. My e-mail is mike.norbury@fmcti.com, drop me a line and I'll send you the program such as it is.

 

[RTyndall]

Mike and Mike,
The error occured as soon as I tried to initialize the form. The problem was the RowSource was set to a named list in combobox at design time. (Hopefully I didn't miss this warning in the thread as I took a couple hours to solve)Once I cleared the property the code worked fine. Thanks. The next problem could be, further down the line I plan to use the ListIndex property for the combobox. Will using this code to populate the box affect the listindex? If so, do you have any suggestions on what to do to acheive the functionality of the listindex and keep the no spaces code? Thanks again for all your help.

Rich.vbaNewbie

PS. Way down the line I want to be able to enter values in a form, generated from a digitizer attached to either a serial port or USB. Any suggestions on where to start?

 

[msmith]

Rich,

Glad you found the solution. After seeing your post I went back and played around some. Indeed, I received the "Permission Denied" error. Interestingly, there is no mention of this possible cause in the Help.

You also wrote: The next problem could be, further down the line I plan to use the ListIndex property for the combobox. Will using this code to populate the box affect the listindex? I'm not sure what you mean. Whenever a ListBox is intialized, its ListIndex is set to -1. Clicking on an item in the listbox at run-time sets ListIndex equal to the item's index (zero-based). There should be no problem using my code. One other point comes to mind. If you use the code to later refresh the listbox, be sure to invoke the Clear method. This will remove all existing items from the list. Go ahead and add this to the

FillList_NoBlanks

procedure.

Re: your P.S. Re-post this question when you get ready to implement. You may want to also post in the Tek-Tips Forums; either Microsoft Office or VBA.

Regards,
Mike