ComboBoxes Collection?

Jun 20, 2006

I am using the following code to determine whether a given range is the linked cell for a Combo Box by looping through the shapes collection.

Function LocateFormControl(OverRange As Range) As Shape
Dim objTemp As Shape
For Each objTemp In OverRange.Parent.Shapes
If Left(objTemp.name, 6) = "Drop D" Then
If WorksheetFunction.Substitute(objTemp.ControlFormat.linkedcell, "$", "") = WorksheetFunction.Substitute(OverRange.Address, "$", "") Then
Set LocateFormControl = objTemp
Exit Function
End If
End If
Next
Set LocateFormControl = Nothing
End Function

However, when I use this code on big worksheets with many other shape objects (such as Comments), the program runs very slowly. Is there a ComboBoxes Collection that I could use to avoid looping through all shapes on the worksheet, or is there a different, faster way to run this code?

View 2 Replies


ADVERTISEMENT

Series Collection Vba

Jan 6, 2010

I trying to code a SeriesCollection in vba, below is the code I have:

' ActiveChart.Name = "Chart1"
ActiveChart.PlotArea.Select

ActiveChart.SeriesCollection(1).Name = "='Chart1'!$C$1"
ActiveChart.SeriesCollection(2).Name = "='Chart1'!$D$1"
ActiveChart.SeriesCollection(3).Name = "='Chart1'!$E$1"
ActiveChart.SeriesCollection(4).Name = "='Chart1'!$F$1"
ActiveChart.SeriesCollection(5).Name = "='Chart1'!$G$1"
ActiveChart.SeriesCollection(6).Name = "='Chart1'!$H$1"
ActiveChart.SeriesCollection(7).Name = "='Chart1'!$I$1"
ActiveChart.SeriesCollection(8).Name = "='Chart1'!$J$1"
ActiveChart.SeriesCollection(9).Name = "='Chart1'!$K$1"
ActiveChart.SeriesCollection(10).Name = "='Chart1'!$L$1"
ActiveChart.SeriesCollection(11).Name = "='Chart1'!$M$1"
ActiveChart.SeriesCollection(12).Name = "='Chart1'!$N$1"

However it it giving the error "Object denfined or Appication denfined"

View 9 Replies View Related

Using INI Files With Comboboxes

Jul 22, 2014

I'm thinking of writing a small tool to store user information on certain files.

The user data will be input by means of comboboxes on a userform.

Each combobox will need to contain a list of values previously used for that combobox. (I'm planning to use a separate ini file for each combobox)

All of the user data entered for a file will be saved into a single ini file.

Most of this is straightforward however I'm not good with comboboxes. And working this in with Ini files makes it harder!

I'm struggling with the following concepts:

SOLVED - How do to make a combobox populate with all the values from its ini file
SOLVED - How to identify if a selected value in any combobox is new (i.e. wasn't in the original list presented to the user)
SOLVED - (From above) How to write this new value to that comboboxes ini file (so it will appear in future lists for that combobox)
How to input two values from any given combobox
(From above) How to record two values for one field into the files ini file.

View 11 Replies View Related

Comboboxes 2 To List 500 & 225

Aug 27, 2009

i have a table on sheet1
A B C
(Names) (Total) (Status)
1. John 500 Active
2. Dave 200 Active
3. Andrew 175 Active
4. John 225 Active

On my userform there are 2 comboboxes. In combobox 1 I have Rowsource as Column A (Names), if I select John I want Combobox 2 to list 500 & 225.

View 5 Replies View Related

Tab Between Worksheet Comboboxes

Mar 1, 2008

I'm sure this must have been answered before but I cant find it, sorry.....

I have 12 comboboxes on a worksheet. They are normally filled out by typing the first 3 characters of one of the entries in the list they are populated with.

I.e. the list starts;

AAC - Al Arish - Egypt - HEAR
AAN - Al Ain - United Arab Emirates - OMAN
AAW - Abottabad - Pakistan -

The first 3 character are unique to each line.

I'd like to be able to enter the 3 characters in the first combobox & then tab to the 2nd combobox, etc, etc. No problem in a userform but how on earth do I do that when the comboboxes are on the worksheet?

View 9 Replies View Related

Two Comboboxes On A Userform

Jan 20, 2009

I have two comboboxes on a userform, they both get there list from the same formula. What I am trying to do is have the second combobox have it's selection preset based on the selection in combobox 1.

ie
Combobox1 = 6:00 AM
when you click on the dropdown for combobox2 i would like 6:00 AM to be the first selection possible, but I dont want it displayed in the box unless it is selected.

Dim timdat1(1 To 85)
For i = 1 To 85
timdat1(i) = Format(TimeSerial(5, (i + 1) * 15, 0) - Int(TimeSerial(5, (i + 1) * 15, 0)), "h:mm AM/PM")
Next i
combobox1.List = timdat1
combobox2.List = timdat1
i am at a loss for where to go from here

View 9 Replies View Related

Defining A Variable As New Collection

Dec 7, 2008

I have a piece of code I am using to important data from a text file. The code works fine but I am having a hard time understanding how defining a Variable "As New Collection" works. I have tried to research on numerous websites dedicated to excel but had no luck finding any further information.

I have executed the code line-by-line over-and-over and believe what the "new collection" does is it creates some type of external database that is hidden within excel (that's just my guess). I have attached a sample file and a text file with dummy data if needed to see how the code is working.

View 4 Replies View Related

Deleting Keys From A Collection?

May 26, 2009

Is there a way to remove a value from a collection after I've added it.

I have simplified the code I'm using to highlight what I'm trying to do.

Basically I'm adding 10 keys to a collection and want to remove the last one. There is a reason for this.

View 3 Replies View Related

Looping With/Through Object Collection

Oct 5, 2006

Im workin with a spreadsheet.. At the end of this script you see how i am putting "a" in R11C33, well I want to put the contents of my workbook C3 instead. I just dont know how to do it. After it does that I need it to move on to cell C4 and put it also in r11c33...

View 4 Replies View Related

Histogram Data Collection

Oct 27, 2006

I have a large table (20,000 plus entries) and I need a way to pull out data that matches criteria in buckets.

For example:
April data (column B) that is from 0 to 30 (column C)
April data (column B) that is from 31 to 45 (column C)
... 46 to 60
... 61 to 75
... 75 to infiniti

Then do it again for May, June, July, etc. I have used DCOUNTs to pull this type of data, but it's a bit cumbersome. Any other ways to pull this type of data from the table?

View 3 Replies View Related

Collection Of Objects - Add Method

Jan 28, 2007

I am attempting to create a double-entry accounting system and am having trouble with my Transaction class (clsTransaction). As each transaction will consist of at least two entries, I have included a Collection in clsTransaction that will accept Entry objects (clsEntry) as items. The Add method appears to go ahead without a hitch, but when I output the values from the Entries I get the same values for all items in the collection. Here is the code I've been working on.

The Transaction Class
clsTransaction - (Class Module)

Option Explicit

Dim mcolEntries As Collection
Dim msDescription As String

Private Sub Class_Initialize()
Set mcolEntries = New Collection
End Sub

Public Sub Add(eItem As clsEntry)
mcolEntries.Add eItem
End Sub........................

View 3 Replies View Related

Collection Of Userform Checkboxes?

May 2, 2007

I have a userform with three checkboxes on it. In the manuals I have read (and websites I am learning from) I have encountered articles that give me the impression that it is possible to create a collection of almost any object, and then quickly return properties about the collection as a whole. So, I am wondering, is it possible (or even appropriate) to group the checkboxes on this userform into a collection, and then, with a few lines of code, see if any of them are checked? If this is not possible, or efficient, is there another way to do it? I'll admit that three IF statements are not all that hard to enter, but I know the day is coming when I will make userforms with many more checkboxes in them.

View 4 Replies View Related

Static Collection Of Textboxes

May 31, 2007

I have a routine that creates a collection of textboxes to simplify adjusting their positions: ...

View 9 Replies View Related

Name Each Name Series Collection Of Chart

Feb 7, 2008

I hope this passes muster. Anyways, I wrote some VBA code to handle an embedded chart in my workbook. I named the chart "Data" but i'm getting some unexpected trouble from VBA. Last time I worked on this set of code, it ran fine. When I try to run it now, it is throwing up an error: "1004 Unable to set name property of series class" The .name function is not working correctly but everything up till that point is working correctly. I have 5 sets of data in this chart also ....

View 9 Replies View Related

Populate Comboboxes And Listboxes Using VBA

Apr 12, 2010

Looking for examples of the following:

1. population a combobox in excel vba using code as opposed from a sheet
2. population a listboxin excel vba using code as opposed from a sheet
3. population a combobox in excel vba using text from a file as opposed from a sheet
4. Getting the values from a combobox on a form to populate cells on a worksheet

These list boxes and comboboxes will be on a form.

View 6 Replies View Related

Using Indirect Function With Comboboxes

Apr 22, 2011

I totally understand how to make the combobox under form controls now but I am not having any success with the indirect function I was using as a list now that I have a combo box. I have attached the current form I am working on that just shows the list function still. How to convert this over to combo boxes with the indirect function?

I attached a second form with the feature I am asking about. It is just lacking the third list that I now have in place. (on the 1st attachment).

Attached Files

File Type: Corp MASTER (3).xlsx‎
File Type: Quote form (2).xlsx‎

View 8 Replies View Related

Populate 5 Comboboxes With Values 1 - 10

Mar 3, 2014

Within a userform, I want to populate five combo boxes and with the values of 1 - 10. I looked through several excel websites and found several solutions. However, I could not figure out how to adopt their solutions to my specific case.

My naming convention for the combo boxes are: cmboPeriod1, cmboPeriod2, ... , cmboPeriod5.

[Code].........

I know my with statement is incorrect. I know the compiler will automatically think 'cmboPeriod' is a variable. I just don't know how to get it 'cmboPeriod & j' to do what I really intend for it to do.

View 4 Replies View Related

Add Comboboxes On Userform At Run Time

May 7, 2009

I have a userform that has a text box. If user puts a number in it and click on proceed the userform must expand and display that many comboboxes. for e.g. if user inputs 8 and then click on proceed then there should be 8 comboboxes on the form. Is it possible to do?

View 4 Replies View Related

Disable Typing In ComboBoxes

Jun 10, 2009

How do I keep users from writing data into ComboBoxes on a UserForm?

View 4 Replies View Related

Multiple Comboboxes One Click

Nov 27, 2009

I have 2 comboboxes created on a userform.

This is my code for both of them

View 13 Replies View Related

ComboBoxes - Getting Data From Different Worksheet

Jul 6, 2012

In one worksheet I have a large amount of data (around 300 columns and 1000 rows), growing bigger each day. This worksheet is called "datas_pH" and consists of column A with different categories, column B with different subcategories, and then the rest of the columns with the pH data.

In a different worksheet (called "database") I have 2 comboboxes where you should be able to select the categories and subcategories. For example, in the first combobox if you select A you should only be able to view and select 1, 2, 3, 4, 5. If you select B you should only be able to select 6, 7, 8, 9, 10. (I believe it may be called cascading comboboxes or something along those lines?).

Anyway, here's a code that works perfectly if the comboboxes and data are in the same data sheet:

Combo Box 1:

Code:
Private Sub ComboBox1_Change() Dim d1 As Object

Set d1 = CreateObject("Scripting.Dictionary")
i = 2 ' start at row 2
x = 1

rowValue = Cells(i, 1) ' set rowValue to the value of the first cell (top data row)

[Code] .....

How do I modify these codes so it will pick up data in the other worksheet ("datas_pH"). Combo Box 1 should pick up data from cells datas_pH!a:a, and Combo Box 2 should pick up from cells datas_pH!b:

View 3 Replies View Related

Filling Comboboxes With Lists

Sep 10, 2012

I'm trying to fill some combo boxes on a userform with arrays stored as variant type (this is how I was told it's done). The problem is, I'm using exactly the same code in two subroutines and it works in one and not in the other. I have a button to reset the form and a subroutine that runs when the form is initialized. Here's the code for the reset button which works:

Code:
Private Sub Reset_Click()
App1Date = ""
App2Date = ""
App3Date = ""
App1Time.Clear
App2Time.Clear
App3Time.Clear

[code]....

At the moment I've got the 'Journeys' and 'TimeArray' declared as public variables for maximum scope, so that they can be used by both subroutines. But I've also tried declaring them localy in each subroutine and it still doesn't work! By using a breakpoint I can see that the line

Code:
carJourneysSaved.List = Journeys

is definitely running when I expect it to but the combo boxes remain blank unless I click reset!

View 1 Replies View Related

How To Link Two ComboBoxes Correctly

Sep 27, 2012

Linking two comboboxes. Actually My department consists of 4 divisions and each division contains 3 sections, that is beside Department manager staff and planning group.

In the attached workbook:

- Combobox1 contains All divisions, Department manager staff and planning group)
- Combobox2 contains all sections of the selected division in Combobox1.

What I want to do is the following:

In Combox1, I want to hide all records not related to the selected division.
In Combox1, I want to hide all records not related to the selected section.

Remarks:

I depend for filtering on the org. code for each division and section.

Fifth and Sixth digits of the first Division's Org. Code are 21

Fifth and Sixth digits of the second Division's Org. Code are 22

Fifth and Sixth digits of the third Division's Org. Code are 23

Fifth and Sixth digits of the third Division's Org. Code are 24

First section's org. code for each division ends with 11

Second Section's org. code for each division ends with 21

Third Section's org. code for each division ends with 31

I have created the following VBA codes to reach the said purposes, the first three ptions in Combobox1 work just fine, but the other options sometimes work fine and other times don't!

Moreover, Combobox 2 is not working at all:

Code:
Private Sub ComboBox1_Change()
On Error Resume Next
Application.ScreenUpdating = False
Select Case ComboBox1
Case Is = [AC4].Value

[Code] ........

View 1 Replies View Related

Dynamic Filtering Comboboxes?

Nov 28, 2012

I am having some difficulty trying to implement the following. Lets say I have a reference list

X 1
X 2
X 3
Y 4
Z 5
Z 6

I want to have two drop downs on my worksheet. One that filters by x,y,z and then based on the option i select there only give me the corresponding values in the second drop down.

Eg if i select X in my drop down I only want 1,2,3 as options in my second drop down. Or if I select Y only the value 4.

View 2 Replies View Related

Mutually Exclusive Comboboxes?

Oct 8, 2009

I have a question about mutually exclusiveness in combo boxes. I know radio buttons are if they are grouped together. I have 2 combo boxes, you can select either 1 or 2.

I want it to be when you select 1 in one of the combobox, it automatically disables you from selecting 1 again in the other combobox. How do i do this without a macro?

View 9 Replies View Related

3 ComboBoxes As Critiria For VLOOKUP

Jul 6, 2006

The syntax to use the values from 3 UserForm ComboBoxes as the critiria for a VLOOLUP statement in a UserForm TextBox?

View 9 Replies View Related

Multiple Comboboxes In A Userform

Feb 17, 2007

I have a worksheet "Master Log" with a UserForm "UserForm2" set up that has 4 comboboxes and 2 text entries. I already have the filtered unique values for each combobox sent to columns "O" thru "R" and they are dynamic ranges. I have the code to populate one of the comboboxes in the userform but cant figure out how to modify the code to have all four populated without getting ambiguous entry errors. And also, do I have to initiate the form for each combobox? Below is the code Im using for a single combobox.

Private Sub UserForm_Initialize()
Dim MyUniqueList As Variant, i As Long
With Me.ListBox1
.Clear ' clear the listbox content
MyUniqueList = UniqueItemList(Range("o4:o100"), True)
For i = 1 To UBound(MyUniqueList)
.AddItem MyUniqueList(i)
Next i
.ListIndex = 0 ' select the first item
End With
End Sub


Private Function UniqueItemList(InputRange As Range, _
HorizontalList As Boolean) As Variant
Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant
Application.Volatile
On Error Resume Next
For Each cl In InputRange
If cl.Formula <> "" Then
cUnique.Add cl.Value, CStr(cl.Value)
End If
Next cl
UniqueItemList = ""
If cUnique.Count > 0 Then
Redim uList(1 To cUnique.Count)
For i = 1 To cUnique.Count
uList(i) = cUnique(i)
Next i
UniqueItemList = uList
If Not HorizontalList Then
UniqueItemList = _
Application.WorksheetFunction.Transpose(UniqueItemList)
End If
End If
On Error Goto 0
End Function

View 2 Replies View Related

Delete All ComboBoxes On Worksheet

Apr 22, 2008

On my Worksheet, I have a Commandbotton that executes this macro to delete all comboboxes in another sheet.

Sub DeleteShapes()
Dim obj As OLEObject
For Each obj In Sheets("Sheet").OLEObjects
If TypeOf obj.Object Is MSForms. ComboBox Then
obj.Delete
End If
Next
End Sub

This method works most of the time, but sometimes I get this error:
delete method of OLEObject class failed. I red in this forum, that I should set the TakeFocusOnClick Property of the CommandButton to false.

View 3 Replies View Related

Separate Sheet Data Collection

Oct 31, 2009

I have a separate sheet(Coverage.jpg) that records some data which at this stage has to be entered manually.

The data comes from another sheet(officers.jpg), each row is 1 flight and the days are usually separated by a blank or grayed row.

I was wondering is there a formula that will collect the data automatically.

As you will see on Coverage.jpg it is broken down into Number of flights(per day), how many flights were covered by 2 or more officers and how many covered by 1 officer.. and then the graph generates off the data.

Is there a formula or something that will enter the per day data?

I have just added some false data to show you how some things get recorded.

View 14 Replies View Related

VBA - Are Named Ranges Part Of Collection?

May 27, 2014

Writing up some code, and I have a workbook with many named ranges. Question is simple...

Are named ranges a part of a collection object that I can use?

Looking to do something like:

[Code] .....

How to determine collection objects on my own in the future!

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved