Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Fill ComboBox With Names Of Named Ranges


The combobox is on a userform. This is the code I'm using to populate the Combobox

For Each nName In ThisWorkbook. Names
ComboBox3.AddItem nName.Name
Next nName

I tried to your reply but i get a object error


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Fill ComboBox With Worksheet Names
I have a form created, were the user can select up to three pairs of open worksheets for sheet subtraction, to see where Im going with, aka the big idea see here: Column Subtraction With Criteria Via Box & Produce Results In New Workbook

What I dont know how to do, is get the form in the attached workbook to show all the available worksheets. Specifically, from the dropdown combo box, I would like it to list all open worksheet title in all open workbooks.

View Replies!   View Related
Fill Combobox With Month Names
I will preface with what I am trying to do. I want to populate a combo box with months. The list will be populated based on a the date range in a dataset, so all 12 months may not bee populated in the combo box. I want the months to be listed in the combo box with their text names displayed(Jan, Feb... Dec) but have their numerical values when selected (1,2... 12). I'm sure there is a way to do this, but for the life of me I can't find how. My initial thought was to assign a value to a specific list index, i.e. assign the value of listindex 6 as "Jun", but I can't figure out the syntax to do that.

This doesn't work, but might give some insight into what I am trying to do

form.combobox.ListIndex(6) = "Jun"

View Replies!   View Related
Fill Array With Userform ComboBox Names
Is it possible to count the number of comboboxes on a userform? Or better yet can I fill an Array with all the combobox names on a userform? I can count the controls on a userform but I'm trying to count just the comboboxes? Instead of all the labels and textboxes and frames.

I would like to loop through them for validation instead of doing it by each one by itself!

View Replies!   View Related
Stop Display Of Names Of Named Ranges When Zooming
Can I stop the displaying of the name of the named area when I am zoomed out of a worksheet? I can not see an option on the Tools > Options > View menu.

A workaround, found by accident, is to make the named area have more than one area.

View Replies!   View Related
Update Multiple ComboBox List Fill Ranges
in A1 there will be a box with LinkedCell set to A1 and ListFillRange set to B1:B5. I now want a similar box in A2, with A2 as LinkedCell. However, when I copy paste the box in A1, it still refers to A1 and B1:B5 (the last part is ok). I can easily change A1 to A2 by just changing it in the properties, but the thing is that I also need these boxes in A3:A100. Someone else helped me out by writing some code, but this is not working correctly yet, probably because I'm doing something wrong.

Also, the ListFillRange might expand in the future. Therefor I would like to know if its possible to select the boxes in A1:A100 and change the ListFillRange to B1:B6 for all at once, or even have a piece of code attached to a command button that recognizes the expanded range and updates it.

I couldn't attach the file because it was to big, therefor I uploaded it with yousendit:
[url]

So in short: I would like all the comboboxes in column E to be linked to the cell underneath and have and option to adjust the listfillrange. The same for column F, but for this one the listfillrange needs to be O8:O11.

View Replies!   View Related
Write Names Of Named Ranges Of Specific Sheet Into Array
I am trying to enter only the named ranges in the active worksheet into an Array.
The amount of named ranges can be from 4 to 7 per sheet.

I'm self taught at VB (This posts on this forum have taught me - thank you!) and have got the below code working on a Workbook - but not on a worksheet level.
ActiveSheet.Names.Count returns nothing, changing to ThisWorkBook.Names.Count returns the sum of all .Names in the workbook.

Sub aTest()
Dim sArray() As String
Dim sJoin As String
Dim y As Long

x = 1
y = ActiveSheet.Names.Count
z = ActiveSheet.Name

View Replies!   View Related
Named Ranges On Multiple Sheets With The Same Named Range & I Cant Figure Out How To Do This
I need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...

View Replies!   View Related
Combobox With Named Range
'i use a combobox in a userform to show data from a hidden sheet its name is sheet5(sheet1).
'i defined the Range as ExpCat and put ==OFFSET(Sheet1!$M$3,0,0, COUNTA(Sheet1!$M3:$M9),1)
'and use this code to view the data, but the code shows the data of the activesheet.

Private Sub ComboBox1_Click()
Me.ComboBox1 = ThisWorkbook.Worksheets(5).Range("ExpCat").Value
'Me.ComboBox1 = ThisWorkbook.Sheet5.Range("ExpCat").Value

End Sub

View Replies!   View Related
Populate ComboBox With Named Range
I use

Set rnData = ActiveSheet. Range("ProductList")
vaData = rnData.Value

With Me.model
.Clear
.List = vaData
.ListIndex = -1
End With

to populate a combobox list. 'ProductList' is a Dynamic Named Range with the definition

=OFFSET(Stock_List!$L$2,0,0, COUNTA(Stock_List!$L:$L)-1,1)

The '-1' allows for a heading at the top of the column.
This works fine when the list in col. L has more than one entry under the heading. But with only one entry I get 'Runtime error 381 - Could not set the List property, Invalid property array index' and the debug highlights on

.list=vaData

View Replies!   View Related
Worksheet Names In ComboBox
What I want to do is have a ComboBox (I think it should be a ComboBox, I want the user to be able to select multiple options at once) and display the names of the worksheets in the ComboBox. I could not find a way to do this directly with rowsource and thought about writing a loop to check the current worksheets with their names and put the values into cells, to this way have the names displayed in the ComboBox via rowsource?

View Replies!   View Related
ComboBox Value To Fill Corresponding Cells
I have a spreadsheet dealing with orders made by customers and the delivery of the items they have ordered.

When I have delivered the items to a customer I need to note down that their order has been delivered. I also need to enter the date on which it has been delivered.
I have created a user form in which I select the order number of that particular order from a combobox and state whether or not it has been delivered, this is either a "yes" or "no" selected from another combobox.
I then have to note the date in a text box on which the items were delivered.

My problem is getting the answer "yes" or "no" and the date to fill in on the worksheet next to the corresponding order number.

View Replies!   View Related
Fill Combobox With All Available Fonts
I am trying to find a quicker way to populate a ComboBox with every possible font available to the user on their particular computer in a manner other than simply using a ton of .AddItem statements.

Is it at all possible to get a user created ComboBox to essentially function the same way that the ComboBox for selecting font works normally? Or is it necessary to go through and add the statements myself?

I am creating a interface to specify default settings, one of which is font.

View Replies!   View Related
Named Ranges From VBA
I have some named ranges that refer to 5 pieces of data organized into a row. For example, the name MyNamedRange might refer to $C$5:$C$10.

I am trying to loop through each column and get the values in MyNamedRange, then change corresponding values in a different named range. However, when I try to use Offset to access the subsequent columns of MyNamedRange, it doesn't work. It only gets the value of the first column right, the rest return <EMPTY>.

Sample .......

View Replies!   View Related
Sum Of X Named Ranges
the code to add two independent named ranges cell by cell in vba. Both of the named ranges have the same structure and the sum would be posted to a third area of the same structure cell wise.

View Replies!   View Related
Using Named Ranges.
How do I go about using named ranges instead of cell names(ie A4:A9)

Private Sub UserForm_Activate()
Dim lngRow As Long
Dim intIndex As Integer
UserForm1.ComboBox1.Clear 'Clear combobox
lngRow = 2 Do While Sheet1.Range("a" & lngRow).Value <> ""
For intIndex = 0 To UserForm1.ComboBox1.ListCount - 1
If UserForm1.ComboBox1.List(intIndex) = Sheet1.Range("a" & lngRow).Value
Then
Goto NextRow
End If
Next intIndex
UserForm1.ComboBox1.AddItem Sheet1.Range("a" & lngRow).Value
NextRow:
lngRow = lngRow + 1
Loop
End Sub

I wanted to change "a" to the defined range "search"?

View Replies!   View Related
Fill Tab Names
I have 100 tabs numbered 1-100. I was wondering if there is anyway to add a tab in the middle and then fill the numbers in somehow.


View Replies!   View Related
Filter Named Lists For Use In ComboBox/Drop-down Box
I have a workbook that lists a number of customers and the products that they use. I am trying to create a lookup that will follow the following process;

1 - User enters (via dropdown selection) Customer Id

2 - User enters (via filtered dropdown based on the 1st selection) Product

3 - User enters (via filtered dropdown based on the 1st and 2nd selections) Pack Size

4 - Product Id autofills itself.

My problem is twofold, firstly I don't know how to perform the "filters" in stages 2 and 3, and secondly I want to remove duplications from all lists at the appropriate times.

View Replies!   View Related
Using Sheet Names As A Named Range
I have a namd sheet for everyday of the year in a workbook, so jan1 jan2 jan3 etc. I have a stats page which brings together certain pertinent data from those pages, so a formula on the stats page might look like this =sum('jan1'!$m$25:$m$900). What I would like to do is to name each day - jan1 jan2 etc as a named range perhaps date1 date2 etc so that the above formula would then look like =sum(date1!$m$25:$m$900).

View Replies!   View Related
Populate Combobox With Range Names
All I want to do is
put a combo box on a spreadsheet,
have it populated, and sorted - ideally, alphabetically with range names already created.

And

I'd like to be able to click on the Range Name and GoTo the location.



View Replies!   View Related
Combobox Populated By Range Names
if a combobox in a userform can be populated by the NAMES of defined ranges in a worksheet?

ie. worksheet contains 3 NAMED arrays: W1 = $C$5:$E$6, W2 = $C$8:$E$9, W3 = $C$11:$E$12. Have the combobox in the userform actully list W1, W2, W3 so that when W2 is picked (for example), cell $C$8 will be selected.

View Replies!   View Related
Populate ComboBox With Sheet Names
I am having trouble with populating a combo box on a user form. I wish for the combo box to be populated using the names of the sheets of the workbook. I am using sheet1 as the control sheet where a user form will open up from this sheet, this will allow me to select the sheet that I require from a combo box that is placed on the form however I do not seam to be able to code the VBA to populate the combo box with the sheet names.

View Replies!   View Related
Populate Combobox With Defined Names
I want to be able populate a combobox with all defined ranges in the workbook?

View Replies!   View Related
How To Elegantly Fill A List / Combobox
I am using the method below to fill a list or combo box. It works but is slow when there are to many entries.

Is there a more elegant way to do this? I heard there was a method using the Excel filter function that makes the whole procedure below not necessary....

View Replies!   View Related
Using A Combobox To Fill Data In Sheet
Using a combobox with dropdown choice you can select today or yesterday. After this i want to fil in cell A1 the date (now) if choose today, and date (-1) if choosen yesterday. I attach a test sheet with this post.

View Replies!   View Related
Fill Textbox With Info From Combobox
I am trying to insert information from a combobox into a textbox. The issue I have is that the information in the combobox is generated by accessing a network folder and filling the combobox with the names of the various folders. The combobox only generates the first seven digits of the folder...

Private Sub combobox1_DropButtonClick()
Call ShowFolderInfo("j:Consultant ServicesState Projects")
End Sub

Sub ShowFolderInfo(folderspec)
Dim index As Object
Dim fs As FileSystemObject
Dim folderObject As Folder
Dim SubFolders As Folders.......................

View Replies!   View Related
Fill Combobox From Multiple Worksheets
I have a combobox that I populate this way:

' Sets Names in ComboBox1 Contents
With Worksheets("Sheet1")
Set rng = .Range(.Cells(1, "A"), .Cells(Rows.Count, "A").End(xlUp))
End With
With ComboBox1
.RowSource = rng.Address(external:=True)
End With

What I have are 4 sheets one of which has 3 columns that I need to combine into one "Master" drop down, is that possible?

Sheet1 - Column A
Sheet2 - Column A, B and C
Sheet3 - Column A
Sheet4 - Column A

View Replies!   View Related
Fill ComboBox With 2 Dimensional Array
I can set up a 2 dimensional array by using

array = Workbooks("Workbook.xls").Worksheets("Data"). Range("D_all")

as range D-all contains 2 columns and 20 rows

I can send that list to a combo box by using

Me.ComboBox1.List() = array

If I have ColumnCount set to 2 then both columns will be listed

If I have ColumnCount set to 1 then the 1st part of the array will be listed

But how do I list just the 2nd part of the array

View Replies!   View Related
Fill ComboBox Based On Selection In Another
I want to dynamically list items in combobox placed in excel sheet. I have a list of 10 values in a column in excel sheet. I have placed two comboboxes in the same sheet. Now depending on the first combobox selected value i want to list either all or only few values from the available 10 values.

View Replies!   View Related
Fill Textboxes In UserForm From ComboBox
I've been trying for hours to populate a textbox on a userform based on a combo box. I'm including a zip of my workbook to see. (don't laugh to hard at my code, like I said I'm green...:) When you select a mix design in my form I want the next six combo boxes to populate. The way I have it now is based on one of the many examples found here, (none of which have worked for me so far, but this one said "the simplest way to do this is......") And I get an error 425 - "Object not found". I've done searches based on the error but haven't found an answer.

View Replies!   View Related
Fill ComboBox With Only 1 Occurence Of Each Item
The ideal is I have a list which the use fills in, for sack of argument Goal 1, Goal 2, etc but I have a problem. This list which the user builds I want to appear in a combobox with is quite easy using the list function and naming the range.

The problem I have is that Goal 1 or 2 can be in this list more than once, if it is at all possible I want or would like the Combobox to only show Goal 1 once and not twice or how many times it occurs. I require the Combobox just to show all Goals once no matter how many time they occur.

View Replies!   View Related
Delete Named Ranges
I have a number of crazy defined names in my spreadsheet (1,746 to be exact). Not sure where they all came from, but I need them gone gone gone. Some of them start with squares at the front of the name and most of them refer to =#ref! or similar. I've seen a number of vba solutions to this, but none of them seem to work for me - I get "400" errors or run time errors. For reference, I'm hitting Alt F11, pasting the whole sub in, saving, then running.

View Replies!   View Related
Referring To Named Ranges
I'm creating a button so as to input a chart where the user inputs the name of the range that they want the chart to hold. The named ranges are dynamic thus I want the graph to also be dynamic but when added the source is simply the range of the name rather than the name itself.

Is there anyway I can enter in a VB variable into quotations marks so that it simply writes that.


View Replies!   View Related
Vlookup With Named Ranges
I am having problems getting the code below to follow my intentions. I have 2 files. I have a temporary file that holds all the data that I need. I have a destination file that needs to have data copied into it. In the temp file, I have data for several dates and product types. What I intend to do is to do a vlookup in VBA to look for the date and the product type in the temp file and copy the appropriate data to the destination file. I have numerous named ranges both in my temp file and my destination file. For the code below, I wanted to make a loop to find the date in the temp file that is listed in the destination file. Once this is done, I wanted to find the product type in the temp file that is listed in the destination file. If both conditions exist, then copy a certain range from the temp file to the destination file. My intention is evident in the code, but I don’t think that I am putting in the correct “code format”.

View Replies!   View Related
Graph Using Named Ranges
Is it possible to create a chart based on value from a drop down?

The drop down has two values which are both named ranges.

There for I want to be able to choose from the drop down whether to display data per team or per agent.

View Replies!   View Related
Named Ranges In Workbook
I have got a named range, which gives me a list of all the students. I would like to put that data returned in named range in excell workbook.

For example:
named range called "Students" is getting populated from A1:A10 with values:
Student1
Student2
Student3
Student4
Student5
Student6
Student7
Student8
Student9
Student10

I would like to put this data in other worksheet.

and instead of putting all the students, it just puts only
Student7
Student8
Student9
Student10

I entered in named range name ("students") in all cells I wanted to put data in.

View Replies!   View Related
Clear Named Ranges
There are over 100 named ranges in my excel 2007 workbook. Is there an easy way to clear all of these out?

View Replies!   View Related
Vba To Change Named Ranges
I was given the
What I am looking to do is have a range of cells that I have named to be used in a data validation for a drop down box. I have named the cells and would like to have the user name the cells by typing in the name into one cell.

For example

Cells F10:F14 are named "orange" right now (typed in cell F9)
I would like the user to be able to change the name of the cells to whatever they want by changing the cell in F9 - i.e. "banana" and the range is automatically named banana so the data validation can search for Banana instead.

The

Dim oldName

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Rng As Range
Set Rng = Range("F9")
If Not Intersect(Target, Rng) Is Nothing Then
Range(oldName).Name = Range("F9")
ActiveWorkbook.Names(oldName).Delete

This Code works great for 1 range - but I have 30 - I can't seem to figure out how to copy it etc for the other 30 ranges.

View Replies!   View Related
Referencing With Named Ranges
Ok I have been trying to figure this out for way to long. Say I have a list of values in A1:A4. I have named this range MyRange. If I wanted to refer to this range somewhere else in the workbook and enter =MyRange it will only work provided the "=MyRange" is in the same rows as the original data.

For example if I enter MyRange in cells C1 through C4 it would mirror the data in column A. But if I enter MyRange in C5:C8 I would get a #Value error. Does anyone have any ideas to make this more of an absolute reference.

Basically anywhere I enter MyRange into a cell I want to see A1. If i drag that formula down I want to see A2, A3, A4, etc....


View Replies!   View Related
Duplicating Named Ranges
I'm creating a KPI spreadsheet which utilizes named ranges to allow for Dynamic charting. I've created the first data input sheet for one of the 10 areas being KPIed. The sheet has 60 named ranges in it.

The goal is to duplicate the existing sheet (Area 1A) 10 times and adjust the named ranges and formulas within the named ranges according to the sheet names.

Is there a way to accomplish this without having to manually recreate or edit every named range for each new sheet?

View Replies!   View Related
Union Named Ranges
(Using Office 2003 on XP Pro) I have two named ranges that I want to union into one big named range so that I can use the big named range in a validation table. Unfortunately the big range does not appear on the list of named range so I switched to VBA to try and lick this but really I was hoping a non-VBA solution exists. Exploring possible VBA solutions, here is what I have so far:

View Replies!   View Related
Named Ranges In A Chart - .
I have a spreadsheet with 300+ questions in columns (covering 2 worksheets - rows 1 to 5), and the answers to the questions are placed in the rows beneath - column A & B lists who the answers came from.

The idea here is that on a seperate sheet a user can enter a question number (e.g: 1.2OP007) and a graph will update to show the different values given for each person who answered that question.

So, in this seperate sheet I have the question in cell $A$1.
In cell $B$1 I have this formula:.........................

View Replies!   View Related
Mirroring Named Ranges
The worksheet contains 20 different datasets which are divided into separate 30 x 10 grids, all of which help comprise a larger 300 x 20 grid. I have defined each smaller grid/dataset as its own named range. Originally the entire dataset started in A1, but I am shifting it dataset down by 40 lines to create space to work at the top of my workbook.

I am wanting to create a data validation list in A1, then have cells A3:J32 populate with the named range I select in A1. However I also want this populated dataset at the top of the sheet to be a mirror -- not a copy.

Therefore once I have the data in my range available A3:J32, anything I manipulate would be mirrored in the original range in the lower portion of the workbook.

View Replies!   View Related
Import Named Ranges
I am importing an external Sheet into my Excel workbook but it only imports the data not the named ranges. I need the named ranges to be imported and overwrite any existing range of same name.

Example:

In existing workbook Sheet I have a named range, ABC which is A1:E10 - in the Sheet I am importing the ABC range is A1:E12. So when I have done import I want named range ABC in my workbook to become A1:E12 (as per external Sheet).

This is the code that import data (but not named ranges)

View Replies!   View Related
Merge 2 Named Ranges
can I merge 2 named ranges with a build-in function and give this merged range a 3rd name ?

View Replies!   View Related
Copy Named Ranges
I have a workbook with about 25 named ranges that I would like to use in a a different workbook. Is there some way to copy them, or a way to transfer them through code?

View Replies!   View Related
Selecting Two Named Ranges
I have two named ranges, LV1 and LV2 and wish to select them for printing. If the ranges were fixed I would use - Range("A2:H4,A8:H10").Select - how do I do the equivalent using the named ranges in vba?

View Replies!   View Related
Create A Set Of Named Ranges
I'm trying to make my worksheet generate a bunch of named ranges whenever it is activated. However, I'm getting a "Not a valid name" error on the line in red. The value of r is a string, so I really don't see what the issue is. Another time, I got a object defined error, but I think I've declared everyhing too. That line is very similar to the line above, and tends to work once (the first time around the loop). After that, I get the errors I mentioned.

View Replies!   View Related
Dynamic Named Ranges ...
Dynamic named ranges - lots of them! ...

View Replies!   View Related
Replace Two Named Ranges
What is the correct syntax if I want to replace two named ranges below to Rng and Rng2 in my code?

View Replies!   View Related
VLookup, Named Ranges And IF
=VLOOKUP('ITEM ADD-CHG Form'!Z19,Brand_V,2,FALSE) Another wrench!. If the value in cell Z19 is not found in the named range "Brand_V", the formula returns #N/A. I need to replace #N/A with the verbiage DEF while leaving the other results intact.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved