Userform - Combo box with Range unexpected results ...

Greetings,

The following code below finds empty cells in any of the columns and selects 
the entire row.
When I run the code and run the range name.select I can see its selected all 
the rows with a blank cell value in them.


Private Sub UserForm_Initialize()

    Dim patientList As Range
    Set patientList = 
Sheets("Data").Range("A:A,D:D,E:E,F:F,G:G").CurrentRegion
    Set patientList = patientList.SpecialCells(xlCellTypeBlanks).EntireRow
    patientList.Select
    Me.comboPatient.RowSource = patientList.Columns(1).Address

End Sub

However when I populate my RowSoure (combo box) its only ever populates one 
value (The first row it sees with a blank cell value)?
If i remove the line which find blank cells it lists all the values in 
column A rather than the selection I require (any rows with blank values I 
want to appear in the combo)

Can anyone please please help me, Im going crazy !!!

Thanks
Rob W







0
Whllrob (18)
11/27/2007 12:39:23 AM
excel 39879 articles. 2 followers. Follow

2 Replies
412 Views

Similar Articles

[PageSpeed] 32

 
When you have a multi area range you must specify each area
or you only get the first area returned.
Looping thru the areas is the common solution.
Here is my test code which seems to work.
Maybe it will be of some help.
-- 
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
'---

Sub Where()
    Dim patientList As Range
    Dim rngArea As Range
    Dim colList As Collection
    
    Set colList = New Collection
    Set patientList = _
    Application.Union(Sheets("Data").Range("A:A"), Sheets("Data").Range("D:G"))
    Set patientList = _
    Application.Intersect(Sheets("Data").UsedRange, patientList)
    Set patientList = patientList.SpecialCells(xlCellTypeBlanks)
    
    For Each rngArea In patientList
        On Error Resume Next
       'Error if a duplicate
        colList.Add vbNullString, CStr(rngArea.Row)
        If Err.Number = 0 Then
           UserForm1.ComboBox1.AddItem "Row " & rngArea.Row
        End If
    Next
    UserForm1.Show
    Unload UserForm1
    Set colList = Nothing
End Sub
'---



"Rob W" 
wrote in message 
Greetings,
The following code below finds empty cells in any of the columns and selects 
the entire row.
When I run the code and run the range name.select I can see its selected all 
the rows with a blank cell value in them.

Private Sub UserForm_Initialize()
    Dim patientList As Range
    Set patientList = 
Sheets("Data").Range("A:A,D:D,E:E,F:F,G:G").CurrentRegion
    Set patientList = patientList.SpecialCells(xlCellTypeBlanks).EntireRow
    patientList.Select
    Me.comboPatient.RowSource = patientList.Columns(1).Address
End Sub

However when I populate my RowSoure (combo box) its only ever populates one 
value (The first row it sees with a blank cell value)?
If i remove the line which find blank cells it lists all the values in 
column A rather than the selection I require (any rows with blank values I 
want to appear in the combo)
Can anyone please please help me, Im going crazy !!!
Thanks
Rob W
0
jim.coneXXX (771)
11/27/2007 2:46:09 AM
Thanks very useful, Ive only just got round to looking at the reply (UK 
based)

I came up with something similar

Dim lRowEnd As Long
Dim R As Range
Dim wsData As Worksheet

Set wsData = Sheets("Data")
lRowEnd = wsData.Cells(Rows.Count, "A").End(xlUp).Row
With comboPatient
    .Clear
    For Each R In wsData.Range("E1:E" & 
lRowEnd).SpecialCells(xlCellTypeBlanks)
        .AddItem CStr(wsData.Cells(R.Row, "A").Value)
    Next R
End With

I will look into your solution in more detail later, thanks again.
I havent used collections before so it should be interesting ..

Cheers
Rob W



"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message 
news:%23saYr%23JMIHA.5224@TK2MSFTNGP02.phx.gbl...
>
> When you have a multi area range you must specify each area
> or you only get the first area returned.
> Looping thru the areas is the common solution.
> Here is my test code which seems to work.
> Maybe it will be of some help.
> -- 
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)
> '---
>
> Sub Where()
>    Dim patientList As Range
>    Dim rngArea As Range
>    Dim colList As Collection
>
>    Set colList = New Collection
>    Set patientList = _
>    Application.Union(Sheets("Data").Range("A:A"), 
> Sheets("Data").Range("D:G"))
>    Set patientList = _
>    Application.Intersect(Sheets("Data").UsedRange, patientList)
>    Set patientList = patientList.SpecialCells(xlCellTypeBlanks)
>
>    For Each rngArea In patientList
>        On Error Resume Next
>       'Error if a duplicate
>        colList.Add vbNullString, CStr(rngArea.Row)
>        If Err.Number = 0 Then
>           UserForm1.ComboBox1.AddItem "Row " & rngArea.Row
>        End If
>    Next
>    UserForm1.Show
>    Unload UserForm1
>    Set colList = Nothing
> End Sub
> '---
>
>
>
> "Rob W"
> wrote in message
> Greetings,
> The following code below finds empty cells in any of the columns and 
> selects
> the entire row.
> When I run the code and run the range name.select I can see its selected 
> all
> the rows with a blank cell value in them.
>
> Private Sub UserForm_Initialize()
>    Dim patientList As Range
>    Set patientList =
> Sheets("Data").Range("A:A,D:D,E:E,F:F,G:G").CurrentRegion
>    Set patientList = patientList.SpecialCells(xlCellTypeBlanks).EntireRow
>    patientList.Select
>    Me.comboPatient.RowSource = patientList.Columns(1).Address
> End Sub
>
> However when I populate my RowSoure (combo box) its only ever populates 
> one
> value (The first row it sees with a blank cell value)?
> If i remove the line which find blank cells it lists all the values in
> column A rather than the selection I require (any rows with blank values I
> want to appear in the combo)
> Can anyone please please help me, Im going crazy !!!
> Thanks
> Rob W 


0
Whllrob (18)
11/27/2007 12:27:58 PM
Reply:

Similar Artilces:

using master page in publication with text boxes
When I use a master page that has text boxes in a publication, how do I get text in the text boxes? John wrote: > When I use a master page that has text boxes in a publication, how do > I get text in the text boxes? =================================== Go to...View / Master Page. Select the textbox. Position your cursor where you want the text to begin. Begin typing. These exact text boxes will appear on every page. -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.com/aczzp Notice This is not tech sup...

How to find folder tree for emails in search results?
Is there a way to see the entire hierarchy of folders for a message in the Search Results window? I just did a search of all mail folders (Outlook 2007). It returned about 20 results. In the "In Folder" column, it only gives the name of the folder the message is in, not the whole folder tree. I have a somewhat complicated folder tree structure and I would prefer not to have to open all of the branches looking for the correct subfolder. In some cases, there may be more than one folder with the same name. Not as far as I know. Search results return the item, not the tree....

Destroying a Modal Dialog Box #2
SomeBody give me a solution for destroying a Modal Dialog Box created by DoModal() I have two ocx controls, switched on a single container. When a modal dialog Box of one ocx is invoked and try to switch to other ocx from the main Window, Previous ocx has to be destroyed and new ocx has to be created. I am facing the trouble of not able to destroy the modal dialog box,if any, invoked from the prevoius control. This is a common problem without any solution. I used the following code to destroy CWnd* pWnd; pWnd = FindWindow(NULL, _T("Alert Me")); if(pWnd) { if(pWnd->Destroy...

What happened to the New Form Dialog box; are there still autoform
...

"An unexpected error has occurred" emailing pub doc
I get this message when trying to email a publisher document that I've sent out before. ...

How to delete the sheet with out dialogue box
Hi, I want to delete the sheet with out dialog box. If i click delete sheet option in my excel, One dialogue box (Data may exist in the sheet(s) selected for deletion. To permanently delete the data press Delete.) is opened. I dont want this dialogue box. How to remove this...? *I want delete the sheet immediately with out dialog box after click the delete option. * Pls help me. Thanks, Ram. -- ramkumar_cpt ------------------------------------------------------------------------ ramkumar_cpt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28673 View this...

Sum based on range values
I need to sum a column A, but only if values in an adjacent column B>0. Therefore the example below should sum to 10 (only including the first 3 rows). A B 7 5 0 4 3 9 0 0 0 0 0 0 8 0 0 0 0 0 Ideally this should be a formula in a cell, not a macro. Any ideas? It doesn't sound complicated, but I can't get my head round it. TIA Ian Thanks Julie. I knew it had to be straightforward. Just a case of knowing which function to use. Ian "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message news:%23$anRGw$EHA.3836@tk2msftngp13.phx.gbl... > Hi > > us...

.NET & XSLT: Unwanted namespace in resulting file after using extension objects
Hello, after using extension objects in the stylesheets there's an additional namespace entry in the resulting file. How can I prevent that? See: ..NET/C#: XslCompiledTransform xslt = new XslCompiledTransform(); .... XsltArgumentList xsltArgs = new XsltArgumentList(); xsltArgs.AddExtensionObject("ibd:DanTe", this); .... xslt.Transform( _source, xsltArgs, target ); XSLT: <?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/ Transform" xmlns:ibd="ibd:DanTe"> &l...

hyperlink button on a userform
[excel 2003] I have a userform which has a column of textboxes displaying hyperlink addresses pulled from a vlookup table. The text boxes have the names : txtHyperlink1 txtHyperlink2 txtHyperlink3 I have Command buttons next to these boxes to which I would like to add code such that they call up the web for the listed hyperlink shown in the text boxes. Can anyone help? Hi Roger, try ActiveWorkbook.FollowHyperlink txtHyperlink1.value Regards, Rainer "Roger on Excel" <RogeronExcel@discussions.microsoft.com> schrieb im Newsbeitrag news:E...

Find Last Row and Name the range
Hello experts, I'm having a problem doing something I thought was rather simple. What I'm doing is copying a worksheet from another file which can have any number of rows in it into the current workbook. Once the worksheet is copied in, I want to name a range that will start on A3:B3 and go to A?:B3. So I need to determine the last row with data in it, and name that range "Units". The code I have below winds up naming the range above A3:B3... So A1:B3 is what ends up being named "Units". Please, any help would be great. Thanks. Dim strLastCell As ...

finding a number witin a range
i have to place a unique number onto a bag. There is a start number and an end number. The numbers are in sequence and no numbers are duplicated. There are approx 5,000 numbers for each job. I need to know if i can find a number witin the range and display the results. Hi Paul VLOOKUP will look for a value and return a related piece of information - so if your bag numbers are in column A and the results in column B you could use the following formula =VLOOKUP(D1,A1:A5001,2,0) where the number you're looking for is entered in D1. Hope this helps Cheers JulieD "paul wither...

test value between range, and month
I cant get the condA or condB to execute if value of A1 fall within range 1 to 10 if(and(A1 >= 1, A1 <= 10), condA, condB) How to get the month with a increment value as below a b c 0 mth inc 2 mth inc 1 2 3 30-may-08 =text(c3,"mmm") will get value of "may" how to get "jul" 4 Hi, Question1: not clear, as written you are missing the = sign in front of IF. But there is nothing wrong with the construction. Tell us what is in A1 and what condA, con...

2007 Lifetime Planner giving wrong results
Ok, I just upgraded to 2007 and my Lifetime Planner Results (the chart) changed dramatically from the previous chart in 2006. It went from a plan that steadily grew, to one that initially climbs slower and then takes a strong nosedive after retirement. In 2006 I was set for life; in 2007 I'm in major trouble. I've noticed several things but I think I've found the major problem. The Planner is configured to get my Expenses total from the Budget (I stayed with the Advanced Budget). Under 2006, that number was about $40K per year. Under 2007 the Expenses is now $64K ($63,12...

Copying list-box control
I need to copy a list box control into about five hundred cells. I need the cell link of the control, however, to change so that it references the cell the control is pasted into. No matter what I do, however, the cell link of the pasted control stays the same (not surprisingly) as in the original control. (And nope, it doesn't seem to matter whether the cell-link reference is absolute [including dollar signs] or not.) If anyone has any ideas, I'd be extremely appreciative--I really don't want to individually format 300 controls. Yuck. What kind of listboxes are th...

Edit box in a dialog box
Hi, I am trying to create an edit box inside a dialog box during runtime, using MFC. Mine is a single document/view application. The way I am trying to do, is as follows. In the OnInitDialog() function of my dialog box(CmyDialog::OnInitDialog()), I declare a CEdit object and call its Create() function. BOOL CmyDialog::OnInitDialog() { CEdit myEdit; CDialog::OnInitDialog(); myEdit.Create(ES_NUMBER | WS_OVERLAPPED | WS_VISIBLE, CRect(0,0,10,10), this, 1); myEdit.ShowWindow(SW_SHOWNORMAL); return TRUE; } I am expecting an edit box...

Xl 2002 Combo Box Problem
I created an excel wookbook in Excel 2000 / Windows 98. When i open i in Excel 2002 / XP, some of the Combo Boxes are flipped upside down When you click them, they right them selves until you click off o them? -- NYSTEC ----------------------------------------------------------------------- NYSTECH's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1457 View this thread: http://www.excelforum.com/showthread.php?threadid=26203 I've heard of this happening with pictures--never with comboboxes. But maybe this will help: http://support.microsoft.com/default.asp...

class wizard belives group box is a button
I use MS Visual C++, Version 6.0. Since I tried to use a component one number control (ActiveX) in my project, which brought the whole Studio 6.0 to crash, i see a strange behaviour. Whenever I drag and drop a group box control from the controls toolbar onto a dialog resource and then try to define a control variable with ClassWizard, it is handled as a button control. How can I re-teach my MSVC++ that the group box is a static, not a button? Any help would be appreciated. How do i get rid of the component one control icon in the control toolbar? Where is the definition file of the stan...

if the cell value exceeds the limit to have a dialog box appear
Is there a way that, if the cell value exceeds a certain limit, a dialog box would appear? Thanks for any assistance. Dave Check Data Validation: Select the cells Data>Data Validation Allow: (check out the number options: whole number, decimal, etc) Does that help? *********** Regards, Ron "Dschro" wrote: > Is there a way that, if the cell value exceeds a certain limit, a dialog box > would appear? > > Thanks for any assistance. > > Dave > Ron, Here's the scenario -- on an Expense Report: If an employee was to fill in any amount in the &...

open dialogue box
Can I print the Open dialogue box? I want to take a picture of all the listed documents in each Word folder to show the date and time they were created. Thank you. D. Ring You can take a screen shot, and insert it as an image into a Word file. Assuming you're using Windows with no other software hijacking the PrintScreen key, display the Open dialog box with the information you need, and press Alt+PrintScreen. This sends a copy of the current window to the clipboard. Then click in a blank Word document, and press Ctrl+V to paste the image into the Word docume...

range to cell
Hello, I am writing a program. I have a excel range object. And I want to point another cell at this range. how can I do that? On the range there is column = 4 row = 3. but I cannot just say =4,3 or something like that? don't you need =D3? why is the column D and not 4? strange =sheets("Sheet1").range("D4") or =sheets("Sheet1").cells(3, 4) -- HTH... Jim Thomlinson "greg" wrote: > Hello, > I am writing a program. > I have a excel range object. > And I want to point another cell at this range. > how can I do that? > On the ran...

To have selected the year on combo box
Hello, The following crosstab query can produce the name of our church offices sideway ( year to year comparison), but how can we make it to seledt the year on combo, and then the report will produce it for the last 5 years: since my landscape report can only accomodate for 5 years while my data is already 10 years These is my query: TRANSFORM First(PelayanJemaatbyYear.NamaPel) AS FirstOfNamaPel SELECT PelayanJemaatbyYear.Nurut, PelayanJemaatbyYear.BidangPelayanan, PelayanJemaatbyYear.CountOfBidangPelayanan, First(PelayanJemaatbyYear.NamaPel) AS [Total Of NamaPel] FROM Pel...

How do I set the print range on a form. Regards
I've created a Timesheet on a form in Excel, but when I print, it only prints a third of the form Enter this into Excel help: Define or clear a print area on a worksheet This should give you a solution "Dave K" wrote: > I've created a Timesheet on a form in Excel, but when I print, it only prints > a third of the form In Excel 200, you would select the are to print, go to "File", "Print area", "Set print area". To make it easier and quicker to print in future, you can record a macro as you print the print area and att...

lookup over multiple ranges
I want something to the effect: =if(iserror(vlookup(a1,range1,2,false)),vlookup(a1,range2,2,false),vlookup(a1,range1,2,false)) but I want to do this without using an if iserror function. Is there a way to do this? An array formula perhaps? Thanks. it is possible, if both Range1 and Range2 contain same number of rows Step1 Define range name Range1 = $e$5:$f$10 Range2 = $k$5:$l$10 .. .. .. Lookup Value placed in H5 =vlookup(h5, indirect("Range"&sumproduct((e5:e10=h5)*1+(k5:k10=h5)*2)),2) <thedevilkaiser@gmail.com> ???????:1164072471.121573.61230@m73g2000cwd.googleg...

unintended result from holding down control key
I held down the control key for a few seconds and a dialog box opened with an accessability feature. I unintentionally turned the feature on and now I must wait several seconds between repeated uses of any key. How can I turn this annoying feature off? Goto Control Panel and select Accessibility Options. Look under the keyboard tab to see which options are selected and turn them off. Probably Sticky Keys >-----Original Message----- >I held down the control key for a few seconds and a dialog >box opened with an accessability feature. I >unintentionally turned the feature ...

How do I name a range and then have that range be dynamic?
I want to select the entire contents of a worksheet, but that data set will change each time I open it as records will be added to it every day. I want to always choose all records, and there will never be any non-contiguous rows in the data or blank rows in it. So I can make a direct call to it and always get all of it by way of the named range. If A1 is the header for example for the first column you can use Range("A1").CurrentRegion.Select The same as Ctrl * -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "CellShocked" <ce...