Ignoring blank cells in a combo box...

Is there any way I can ignore blank cells when using a combo box fro
the forms menu?  For instance, my combo box is being filled by th
range A1:A5.  However, if A3 is blank, I don't want it ( a blank) t
show up in the drop down box.  Is there is a more efficient way to d
this or it might it be more simple to use a combo box from the contro
toolbox?

Any help would be appreciated.

Thanks

--
Message posted from http://www.ExcelForum.com

0
5/13/2004 3:37:15 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1106 Views

Similar Articles

[PageSpeed] 50

The combobox from the Forms toolbar is also called a DropDown.

And you could use a little code that filled up that dropdown:

I chose to put it into the worksheet_activate event.  But you could put it
whereever you need:

Option Explicit
Private Sub Worksheet_Activate()
    Dim myRng As Range
    Dim myCell As Range
    Dim myDD As DropDown
    
    Set myRng = Me.Range("a1:a5")
    Set myDD = Me.DropDowns("drop down 1")
    
    myDD.RemoveAllItems
    
    For Each myCell In myRng.Cells
        If IsEmpty(myCell) Then
            'do nothing
        Else
            myDD.AddItem myCell.Value
        End If
    Next myCell
    
End Sub


Debra Dalgleish has some neat ways to use Data|Validation.  She has one that
hides previously selected items.  Maybe you could change it to hide the blank
values.

http://www.contextures.com/xlDataVal03.html

"Rutgers_Excels <" wrote:
> 
> Is there any way I can ignore blank cells when using a combo box from
> the forms menu?  For instance, my combo box is being filled by the
> range A1:A5.  However, if A3 is blank, I don't want it ( a blank) to
> show up in the drop down box.  Is there is a more efficient way to do
> this or it might it be more simple to use a combo box from the control
> toolbox?
> 
> Any help would be appreciated.
> 
> Thanks!
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/14/2004 12:33:50 AM
Reply:

Similar Artilces:

Create a multi-select list box with a NotinLIst procedure
Hi I would like to create a multi-select list box that will allow the selection of multiple values. I would also like to include a NotInList procedure that will add new values if needed. The list box is based on a separate table which I called LB_actions. Is this possible? Also, how do I sort the entries after new entries have been added? Thank you in advance On Thu, 25 Mar 2010 09:25:01 -0700, forest8 wrote: > Hi > > I would like to create a multi-select list box that will allow the selection > of multiple values. I would also like to include a No...

if cell a gets this value then cell b receives this value: how to
the point scale is 300 points. the value in column b is on a 100 point scale. If they do not reach 180 points on the eval, then they receive an unstisfactory rating. 180 is 70 points. every point there after in the evaluation reaises the value in column b by .25. 181=70.25 182=70.50 this continues on until the max of 300 eval points for a 100% rating in column B. Does that help? -- static69 ------------------------------------------------------------------------ static69's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11075 View this thread: http://www.e...

Separate macros for each cell.
Hi all, Ok, this is kinda difficult to explain but i will try to. I have a workbook with 7 sheets inside. Each sheet accounts for one big category for example Air, Noise, Pollution. Take the "Noise" active sheet for example, it has a Row named Procedure/Training in which i want to enable users to input info. This info is DocumentType and DocumentName. For this purpose, i have created a user form with combo box for DocumentType and text box for DocumentName plus "Ok","Cancel" and "Clear Form" command buttons. The macro works fine except for it only retur...

automatically select cell in another column
Hi, I have a question. Suppose you have three columns. R1 Col1 Col2 Col3 R2 5 100 20 R3 10 200 R4 25 300 R5 30 50 R6 35 0 R7 40 0 I want to calculate the value in Col3 (20 in this case) with a value in Col1 for which the corresponding value in Col2 is just before 0. That is, Multiply 20 with 30 (R6Col2=0, so select R5Col1). Now if I change the values in Col2, the calculated result should reflect the change automatically. I am wondering if it is possible to do this in Excel, peferably without writing a macro! Thanks. -ab Hi try the following (if...

HELP! How to copy cell with leading zeros &/or fixed length ??
I need to copy the daa from several cells to one. I need to maintain specified character length or numbers with leading zeros in each cell after I combine them into a single cell. I'm trying to use =C1&C2&C3 Example: Cell Length Contents C1 5 00067 C2 3 XY C3 6 abcde so I should end up with 00067 XY abcde after I combine them. Any help appriceated. thanks. try =F7&TEXT(F8,"00000") -- Don Guillett SalesAid Software donaldb@281.com "tmb" <topmailbox@yahoo.com> wrote in message news:o1X%d.202663$qB6.152820@torna...

populate cell from worksheet name?
i am wondering if it is possible to populate a cell from the worksheet name? thanks! Hi try one of the following formulas (note: the workbook has to be save before). Just use the formulas as they are shown (don't replace 'filename' with anything else) File path and file name: =CELL("filename",A1) File path only =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) File name only =MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CEL L("filename",A1),1)-FIND("...

Can OWA and Blackberry Enterprise reside on the same box?
I know this would not be best practice but can these two applications reside on the same box? On 8 Aug 2006 14:34:51 -0700, "vanilla" <espaul@rscquality.com> wrote: >I know this would not be best practice but can these two applications >reside on the same box? I believe so. I would not recommend it unless you have a small user base. For a few reasons... RIM (BlackBerry) Support might not support it - check with them. Also, if your RIM server crashes for some reason and you have to restart, you're also restarting the Exchange server. Last (and for the most imp...

Linked combo boxes
have a dog show registration form created in Excel 97 I have a database named ShowTable in which I have columns for Club, Secretary, Address, Town/City, Postcode (zip) On the main form, I have a combo box which is linked to Club column and this places the club name in a separate cell (say c23) What I would like is to have the Club name linked with the correct Secretary name (to appear in say C24) And the rest of the information to do follow the same procedure (say (C25 onward) I have tried VLOOKUP but this only returns me one set of names (Secretary, Addresses, etc) and does not link with the...

Copy contents from one cell to another
Is there some Function that will move the contents of one cell to another? I want to copy data from a static cell to another cell based on the value in the contents of another cell. For instance, I have a list of names in Alphabetical order in column 'A'. I have a list of whole numbers from 1 to 50 in column 'B'. The numbers in column 'B' will change order randomly when I recalculate (F9). I want to use this number to reference the cell in column 'A' and copy that name to column 'C', starting at 1 and work thru 50, thus jumbling the names. If ...

Applescript
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hello, I'm using Word 2008. <br><br>how I can to make an applescript which paste text into a text box or a frame? the text will be in the clip board or in a variable. I try with the following code, but it doesn't works. <br><br>insert text MyText at text box 1 of active document <br><br>Thanks!!!!!! Start with something like tell application "Microsoft Word" set MyText to "my text" set content of text range of text frame of text box 1 o...

Disallow File Save if No Input in Cells?
Is there any way to require data entry into cells such that the save/save as functions can be disabled until the data is entered? Thanks, Dan -- Dan Raab ------------------------------------------------------------------------ Dan Raab's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29414 View this thread: http://www.excelforum.com/showthread.php?threadid=499474 You can use the BeforeSave event procedure to do this. In the ThisWorkbook code module, use the following code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ...

How do I have an active cell highlight automatically
I am working in excel and having trouble seeing the active cell. I would like to have the active cell highlighted. Is this possible? It would make it possible to find the active cell easier. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.FormatConditions.Delete With Target .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 5 End With With .Bo...

The From box
Every time I create a new email the "from" box appears. How do I turn this off. Create a new message; View-> uncheck From Field -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Navigation Pane Tips & Tricks -Create an Office XP CD slipstreamed with Service Pack 3 ----- "Mike" <anonymous@discussions.microsoft.com> wrote in message news:26fb01c4703e$1ee1e320$a301280a@phx.gbl... > Every time I create a new email the "from" box appears. > How do I turn this off. ...

Combo boxes #5
I would like to have a combo box on my worksheet, from which a user ca select a date. It needs to default to the current date, but the lis needs to include dates from the past two weeks and the next two. Unfortunately, I have little idea how to go about achieving this - I' not even sure which event I need to attach code to. Any help appreciated -- Message posted from http://www.ExcelForum.com Hi, How about using a calendar itself? you can insert the calendar control (Insert>object> calendar control) then add this macro code, which will bring up the calendar in cells A1:A20 (...

paste spans several cells
Hello, I want to paste text from an email message into a spreadsheet. The email text is several lines. When I paste it goes into several cells instead of just one. I've tried paste special and chosen unformatted text and it does the same thing. Any thoughts? Dear Jared, If you want all the text goes into 1 single cell, you paste it into the "Formula bar". then you should squeeze everything into 1 cell...... HTH "Jared" <jared_k@earthlink.net> wrote in message news:e2c36299.0311070813.691dc829@posting.google.com... > Hello, > I want to paste text from ...

Start with blank workbook
I got a new computer and I now when I open Excel, it does not start with a blank workbook. How do I setup Excel to open a blank workbook each time, ( like I used to have it set up)? Windows 2000 Excel 2000 What does it start with? An existing workbook? You may want to check if you have any workbook files in your XLStart folder. And then check to see if there is a reference to a folder under: Tools|Options|General|"At startup, open all files in". Most people don't use this, because too many files get opened when xl starts. scott wrote: > > I got a new computer...

How to remove or replace a carriage return character in a cell?
After importing a Cognos "hotfile" into excel, the cell contains a carriage return character (looks like a square) and I want this to be a new line feed. I have tried to determine how to do a find and replace, but no luck. Help! Saved from a previous post: You can use Chip Pearson's Cell View addin to find out the character it is: http://www.cpearson.com/excel/CellView.htm If those box characters are char(10)'s (alt-enters), you can use edit|Replace what: ctrl-j with: (spacebar??) replace all If that box character is something else, you may need a macro: Option Ex...

link a combo box to a picture
Hello, I have a form that I enter information into 3 txt boxes and it gives me an answer in another text box then I click view and it brings up a rpt that gives me the list of 10 barcodes I need and directions on how to enter some info from the form and it shows me a picture of scanable barcodes. What I want to do is on the form when I select an entry from on of the combo boxes (cboGuage) and when I click to get to the report it will show me the barcode related to the number selected from the cboGuage list. I have 10 numbers in the cboGuage list and 10 barcodes that coraspond to these n...

Hide and unhide shaded cells
Hi everyone! New here and in Excel too. I want to hide shaded rows (different colours) and see only unshaded rows. Later I will unhide those rows. Thanks in advance for your answers PS. I search the forum but no luck -- Sylve Are you ready to embark on learning a little Visual Basic? What version of Excel (2007 or before)? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sylve" <Sylve.3f6df46@excelbanter.com> wrote in message news:Sylve.3f6df46@excelbanter.com... > > Hi everyone! New here and in Excel ...

Insert message when highlighting a cell?
Please advise how when placing a pointer above a cell, Excel can be made to give an explanatory message to the person using it? Cheers Colin Hi Colin! Use: Data > Validation -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Colin" <colincurious@hotmail.com> wrote in message news:bbb06f96.0401041224.1eae9c8a@posting.google.com... > Please advise how when placing a pointer above a cell, Excel can be > made to give an explanatory message...

How do I update links based on a cell value?
I'm trying to update links based on a cell value. For Example: A1 thru A4 has the values 21,22,23, and 24 respectivly. My link in B2 is ='C:\temp\[Cash Report 12-22-05.xls]Sheet1'!$D$5. Is there a way that I can have the link in B2, change to 12-23-05(A2's Value) without doing a manual find and replace for each cell? If there is a macro way to do this that would be great too. Thanks in Adavance! Vick If the resulting file name will be open, you can use =indirect(). But that will fail if the "sending" workbook is closed. How about just Edit|Links|change ...

no password/cells will not accept cursor for change
no password cells will not accept cursor for changes will not let me open new workbook icons at top of page are not accessable ...

Event Id: 1005 Please Don't Ignore Me
Hi there, For some unexpected situation one of my Exchange 2003 servers services stop yesterday. I visit the Microsoft site to look for the event log and can find a source of information. I’m running Windows 2003 Standard Edition, SP with Exchange 2003 Advance Edition with SP 2. Here is the event id I’m receiving: Event Type: Error Event Source: MSExchangeSA Event Category: Monitoring Event ID: 1005 Date: 9/5/2006 Time: 4:10:16 PM User: N/A Computer: ESD1MXOLYMB02 Description: Unexpected error <<0xc1050000 - Network problems are preventing connection to the Microsoft Exchang...

Linked Text Boxes
I am using Publisher 2002 to create a newsletter. I used the design layout wizard for the style and I am trying to update the text boxes. When I change the text box to a different font on Page 4 it Changes my headline on page 1, or if I delete it on 4 the main headline is deleted on Page 1. I know that these two (and possibly others) are linked but I don't want that. How can I unlink these text boxes without deleting them and recreating them. I might be using the wrong terminology here but the two boxes update each other. This is a really annoying prospect that I might have to co...

Mouse highliting excel cells
I have and issue with my mouse high lighting a cell and it will not release the cell no matter what I try, I even tried the F8 key and this still dose not help, any ideas??? Try this, rocker: http://www.officearticles.com/excel/cursor_keeps_selecting_cells_in_microsoft_excel.htm ************ Anne Troy www.OfficeArticles.com "rocker" <ebruegger@gmail.com> wrote in message news:1129219985.489785.188420@g14g2000cwa.googlegroups.com... >I have and issue with my mouse high lighting a cell and it will not > release the cell no matter what I try, I even tried the F8 key and...