I have a name list on sheet2, column A that will have names added to and removed from. Everytime this list is modified it sorts in ascending order and creates a new sheet named after the name added. These items are added to an ActiveX combo box on sheet1. I need to know how to link the item in the combo box to the corresponding sheet(ex: item: "Jim", sheet name: "Jim").
View 2 RepliesI have a workbook with a combo box.
ActiveX control? (=EMBED("Forms.ComboBox.1","") They are filled using named ranges on another worksheet and work fine. The problem happens when I share the workbook the combo boxes become completely non-functional. They don't even drop down when clicked.
I have a table in sheet1 in excel containing 10 rows. I want to copy the first column of this table into sheet2 in the same workbook but what I want is that in case I inserted a row or more in Sheet1, I wanted to be copied to sheet2 automatically. How can I do this?
View 1 Replies View RelatedI have two excel sheets, one has partial information (displayName, title, company, streetAddress, city, state, postalCode, Pager, homephone, fax) the other has the missing information that I need on the first sheet.
Sheet 1 (DB1, has partial info) and Sheet 2 (DB2, has the missing info). I need to somehow link these two, and what they both have in common is name. DB1 has "displayName" which is just First Name Last Name (e.g. John Smith). DB2 has First Name and Last Name, but i'll concatenate that to a new column named "displayName" ( which I assume will be needed to link? ).
The missing information in DB1 is title / streetAddress / city / postal code. DB2 has "Location" (which is a company code, and I want to replace the code with the address, city, postal code) and "Group" (which is title).
To make it easier, I could just do a find and replace on DB2 (e.g. A01-DFW-HWY67) and do it that way right? Or is there any easier way to do that?
Other than that though, how would I link DB1 and DB2, using displayName and fill out DB1 with the information from DB2?
successfully use Activex combobox on the worksheet, (excel 2003)
without the code from other boxes being triggered at random.
Or should I give this up, and use a form to house these controls? ( I have no Knowledge on this)
I've tried certain IF then statements to protect the excecutable, but it seems, that, even boxes in other books are looked at, with a simple call statement (to a macro in module)
Having a real problem in Isolating these things.
Try to get the code to activate when you select the same Item twice, It seems none of the procedures do this. I made a workaround with using two procedures, but it's a bit clumsy.
I've created a workbook on whose main sheet there are three activex comboboxes. The first one contains a list of years, and the second one contains a list of months. The user selects the year, and it goes to its linked cell. The user then selects a month, and using the Combobox_Click event, should run a sequence of 7 macros (i.e. using the "call updateinformation1" macro, which is the first of the seven updateinformation macros).
The sequence of macros ran fine before I added the comboboxes. After adding the comboboxes, the final (7th) macro somehow goes back to the first macro and then causes a "ClearContents method of Range class failed" error on the "Selection.ClearContents" line. These are the first and seventh macros:
Sub updateinformation1()
'clears previous information to make way for new information
Application.ScreenUpdating = False
Application.EnableEvents = False
Worksheets("Criteria2").Activate
Cells.Select
Selection.ClearContents
I can't for the life of me figure out why it's looping back to the first macro instead of just stopping after the seventh. I have other workbooks that do something similar, and I don't have this problem. Am I missing something?
if I need to post all 7 macros?
I have an application were I would like to use an Active X combo box instead of a regular dropdown list. The list contains 64 items and the text becomes very small in a standard dropdown list. Using an Active X Combo Box allows me to enlarge the text for easier viewing but with 64 items the list tends to run off the sheet. Is their a switch or some other way (scrolling) to make it more viewer friendly?
View 5 Replies View RelatedI am trying to use a combo box with a defined list I have in another worksheet.
I was able to name and populate the list into the combo box, but am trying to work a macro into it.
I have a large data sheet with with many columns. In this worksheet, I also placed the combo box. What I would like to do is to be able to click on a name in the combo box and have it filter Column H, where there are many data points.
For example, In column H I have Apples, Watermelons, Peaches, etc.
In the combo box list, I would like to click on Apples and Column H list only the Apple names. Then if I want to click on Watermelons, it would filter Column H to only those which have Watermelons listed...
I have 100 combo boxes on an excel sheet, they are divided in three sets (1-25, 26-50, 51-100). Each set has the same value within the set, but is different from the other.
Right now I am able to populate the combo boxes using the following code
Code:
ActiveSheet.ComboBox1.AddItem "Value1"
ActiveSheet.ComboBox1.AddItem "Value2"
ActiveSheet.ComboBox1.AddItem "Value3"
^ This is time consuming and error prone. Is there a way I can use a variable to populate the combo boxes?
I have been able to find the following code, but it does not work.
Code:
Dim ComboBox As ObjectFor ComboBoxCalc1 = 1 To 25
Set ComboBox = ComboBox(ComboBoxCalc1).Select
ActiveSheet.ComboBox(ComboBoxCalc1).AddItem "Value1"
ActiveSheet.ComboBox(ComboBoxCalc1).AddItem "Value2"
ActiveSheet.ComboBox(ComboBoxCalc1).AddItem "Value3"
Next
How do I set the font size property and special effect property in this code?
I was trying to answer a question on Experrt Exchange but Rory beat me to it and now I'm feeling frustrated that I can't get my solution to work.
Sub AddCombo()Dim rVals As Range, rCell As Range, lTop, lLef, lHeight, lWidth, lCount As LongSet rVals = ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)lCount = 1For Each rCell In rVals If rCell.Validation.Type = 3 Then lTop = rCell.Top lLeft = rCell.Left lHeight = rCell.Rows.Height lWidth = rCell.Columns.Width With ActiveSheet.OLEObjects.Add(classtype:="Forms.ComboBox.1", Left:=lLeft, Top:=lTop, Width:=lWidth, Height:=lHeight) .Name = "NewCombo" & lCount .ListFillRange = rCell.Validation.Formula1 .LinkedCell = rCell.Address(0, 0) '.SpecialEffect = fmSpecialEffectFlat //doesn't work // '.FontSize = 14 //doesn't work // '.Font.Size = 14 // doesn't work // End With lCount = lCount + 1 End IfNext rCellEnd Sub
How do I reference the value contained within a combobox using VBA?
I have tried the following:
ActiveSheet. OLEObjects("cmbSearchType").Object.Value
ActiveSheet.OLEObjects("cmbSearchType").Value
Sheets("mySheet").OLEObjects("cmbSearchType").Value
Sheets("mySheet").OLEObjects("cmbSearchType").Object.Value
I'm wanting to use the drop-down combo box as a simple way for the user to choose what type of search they wish to perform on the spreadsheet. The search algorithm chosen depends upon the current value show in the combo box.
I am using Excel 2007.
Since Form Controls comboboxes don't allow font editing, I need to switch to ActiveX...
The combo box values come from the following cell range:
tbl_arrays!$I$3:$I$6
Those values are, from top to bottom: "select distance", "50 Miles", "100 Miles", "150 Miles".
The following macro is also assigned to the combobox:
[Code].....
I have two issues. The first is that the macro doesn't seem to be working. It is supposed to filter a list of cities within 50, 100 and 150 miles of a user input zip or city. The original Forms Control combo works but not ActiveX after I put the code in the change event:
[Code] .........
The second issue is that the last clicked value in the ActiveX comboBox replaces the top one.
Ex: from top to bottom, the combobox should display:
[Code] ........
but if the user clicks "100 Miles", the combobox is then going to display
[Code] ......
If the user then select "50 Miles", the box displays:
[Code] ........
I managed to put the names of my other worksheets into some comboboxes in the main worksheet by using this ....
View 12 Replies View RelatedI have a golf teetime reservation spreadsheet. For each tee time there can be up to four players names so I use 4 columns. Times are listed in rows every 10 minutes beginning 6am until 5pm for a total of 66 rows per day. I have a member/customer list that I have on a separate sheet that I've named. I am using the ActiveX combo box next to each possible entry (so 4 columns times 66 rows = 264 combo boxes). It's a little slow and takes a long time to open. I like the ActiveX combo box because it allows me to define the box format and display and it allows search/read/write capability. The idea is the customer calls, I ask what time they want and we arrive upon an available time. I ask for a last name, I click the combo box, type the first letter of the first name and then scroll if necessary to the name and click it to add the name to the time/player cell. If no name in my list we simply type it in and I'll capture it next time I refresh my customer list.
way to dynamically define the linkedcell in the properties so that instead of 264 combo boxes, I might have one combo box that I can click on and then direct my selection to the cell I want it to go into.
I am trying to change the font size on my combo box using VBA.
I create the combobox dynamically, so need to be able to change the font too.
This is how I create the combobox, however the .Font.Size = 10 does not work and the default font size is 11.
Code:
With ActiveSheet .OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=159.75, Top:=80.25, Width:=75.75, Height:=19.5) _
.Font.Size = 10
.Name = "cmbBaseD"
.OLEObjects("cmbBaseD").ListFillRange = "DynRng"
End With
I found a code on [URL] that claims that it will do what I want to achieve. I tried it but it is not working for me. Here is the code
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
[Code]....
How to verify that this code is a working code and not just bogus one?
I've searched the web and OzGrid all day, and still have this question: what is the equivalent for .OnAction when using a ComboBox as shown in the code below. Everything else works as desired.
Loopcntr = 1
Set xyz = ActiveSheet. OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, _
Left:=Target.Left, Top:=Target.Top, Width:=Target.Width, Height:=Target.Height)
'''??? xyz.OnAction = "TDListing"
xyz.Object.BackColor = RGB(204, 255, 204)
Do While Loopcntr <= TaskListArraySizeHolder
DDholdName = TaskListArray(Loopcntr)
xyz.Object.AddItem DDholdName
Loopcntr = Loopcntr + 1
Loop
I have an activeX combo box with dates in the following quarterly format (dec-14, march-15, july-15 etc), with the following code:
[Code] .....
However' while each code works fine standalone, the two codes wont work together - and the listindex reverts back to 0.
=AVERAGEIFS(MFI,DATE,">="&JanStart,DATE,"<="&JanEnd)
MFI is the named ranged that is referencing a column of data. This works, but I want to be able to change the MFI to a different named ranged.
So what I am trying to do is using an ActiveX Combo box so I can reference the named ranges that I have set, for example MFI, so each time a user chooses MFI or another named range, it will output to a linked cell (A1). Then the linked cell will be referenced into this forumla.
=AVERAGEIFS(A1,DATE,">="&JanStart,DATE,"<="&JanEnd)
Is it possible for A1 to be linked to the named range, so if the user choses MFI in the combo box, it will change all AverageIFS formulas to look at a different named range.
Here is my code:
Code:
Private Sub ComboBox1_Change()
ActiveSheet.Rows("3:62").Hidden = False
Range("D1").Value = ComboBox1.Text
End Sub
I get a runtime error '1004' when I make my combo box selection. I don't have any security or password protection type stuff. The combo box is part of a more complicated spreadsheet and I don't want to switch to a data validation type drop down and I really want it to be activeX.
I'm trying to link objects and pricing to groups via a combo box. If the user selects Group A from the combo box and enters Object A on the line item, I want the price associated with Object A and Group A to populate.
Group A
Group B
Group C
Group D
Object A
$1
$3
$5
$7
Object B
$2
$4
$6
$8
Object C
$3
$5
$7
$9
Object D
$4
$6
$8
$10
A B C
1Timber$200A-rating
2Metal$500C-rating
3plastic$100B-rating
A1:A3 was input into combo box list. If I select an option in combo box, how can I make B11 and B12 to update data accordingly? (ie, if Metal was selected, B11 should show $500 and B12 should show C-rating)
On a userform, is there a way in which to set a scrollbar's slider postion to the ListIndex value of the item chosen in a ComboBox?
In other words, if the total ListCount for this choice is is ten, and the item chosen in the Combobox is in the fourth position, can the scrollbar slider automatically be set to the fourth position rather than be all the way to the left or right? I would think that this would make it visually easier for the user to understand in which direction they can scroll the list. Here's what I've got so far:
I have three combobox...if user click in the option 1 the combobox2 change the values to show and when the user click en one of the option of combobox2 change the options of the combobox 3
If combobox1.value = 0 Then
Set c = Sheets("Codigos de Area"). Cells(1, 2)
While c.Offset(x, 0).value <> ""
ComboBox2.AddItem c.Offset(x, 0).value
x = x +1
Wend
And I can change the code for one select case.
Using the developer tab I inserted an Active X combo box. Under properties I referenced a 'linked cell' that I want the data to appear in. This box works great as long as I keep the sheet open. Once I save and close the workbook and re-open, that reference is lost. It shows #REF! in that field. Why does it lose the reference? I have tried to reference a cell on the same sheet as well as a cell in a different sheet and it keeps the sheet name but not the individual cell.
View 13 Replies View RelatedI need to have a single combobox pull values from a very large term list (>40,000), and place selections in a single column in sequential cells (eg, B1, B2, B3, etc..). With the following code (credit to Ingolf and MickG) I am able to get the list to popluate cells in a column. However, I cannot seem to find the proper attributes to control the auto selection of items in list. I do want the autocomplete feature so the user can quickly navigate this huge list, but as soon as i start typing, it starts populating terms into the growing list. I would like to have the user actually select the item.
Private Sub Worksheet_Activate()
Dim Rng As Range
With Sheets("Listing")
[Code].....
I have 25 sheets in the workbook and a combobox on the main page, The combobox references a range of 1-25 that represents the 25 hidden pages. right now i can get the sheets to unhide one at a time based on the selection e.g. combobox option 1 will unhide sheet 1 but the sheet are representing sites in a design so i need to have the option to select multiple sites in the combobox option so for example if i select 5 then sheets 1-5 should unhide. I hope I've explained that clearly.
The other question or option would be to just duplicate sheet 1 based on the combobox selection e.g. selection 5 duplicates sheet 1 5 times.
im currently trying to link a commandbutton1 (calckatz) on one sheet to activate another commandbutton2 (megkatz) on another sheet.
the code for commandbutton2 is
Private Sub MEGkatz_Click()
'Meg calcs KATZ
Range("F6").Select
Range("F6").GoalSeek Goal:=Range("F5"), ChangingCell:=Range("D9")
end sub
I have a master tab that has data grouped as follows
Column 1
title of a section (written once then left blank for the rest of a section)
Column 2
numerous descriptors (each in a new row)
Column 3
The response to the descriptors in column 2
I want to link entire sections from column 2 and 3 into different excel files so that if I make changes to column 2 or 3 in the master tab, the changes are made to all tabs. If there is a way, I'd like it if I add a row to the master, it is automatically added to the others.
Okay, now on to the question:
I have three sheets "Contacts"/Sheet 1, "Judges"/Sheet 2, "Sponsors"/Sheet 3. Row 1 and 2 have been used as headers in each of them.
I would like to be able to enter a value in A3 of Sheet 1, such as 'judge' (a number would work fine too), and have the contact information in the same row (columns B-M) copy into the next available row of Sheet 2 in columns B-M.
I don't know if a formula exists for this but if I could type anything in the formula box and have Excel be smart enough to figure out what I'm asking I'd type something like this:
"Hey Excel. Follow this formula: IF 'judge' in sheet 1, A3; THEN copy sheet 1, B3-M3 to sheet 2, B-M next available row...Then do the same thing for 'contacts' but copy into sheet 3."