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
For some reason, when i use my arrow keys to move from cell to cell it now only scrolls my screen around....What did i do, and how do i change it back? I find it sad that i can write code and this stumped me.
View 3 Replies View RelatedI am relatively new to Excel 2007 and my arrow keys do not navigate cell by cell. When I press the right or left arrow keys, my entire screen shifts to the right or left but the cursor STAYS in the cell where it was. Likewise, when I press the up or down arrow keys the screen shifts up or down but again, the cursor remains where it was. Is there an option somewhere that I need to change?
View 3 Replies View Relatedsuccessfully 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 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.
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 Replies View RelatedSince 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.
I 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.
=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 have seen some code around that allows scrolling in listboxes using the mousewheel. I have also seen some code here on the forums about scrolling through a combobox embedded in a workbook. I have tried adapting both of these to suit a combobox on a userform but I cannot get either to work.
Here is the code that I used for the embedded combobox; I added it to the base code for the userform
I then added the following code to a standard module as stated in the forum post;
In the first section of the code there is a msgbox that never gets shown so I don't think code is recognizing the mouse wheel at all.
The second set of code that I used was set up to work with a listbox and it works perfectly with a listbox control. The problem is I cannot adapt it to suit a combobox and if I use a breakpoint to see what is happening Excel freezes.
The following code is added to the userforms code;
Then in a standard module I added the following code;
I added this section of the code;
I think this should only work on a Combobox but a Combobox does not appear to support the TopIndex value. Is there a way this can be setup to work with a userform combobox?
Sorry about the huge amount of code.
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 am using the toolbox/ properties method to restrict the scroll area in the sheets of my workbook. However, for some sheets when i keep the right hand arrow pressed, the screen will not scroll to the last column. (i.e. the screen display will not move with the selected cell box)
View 2 Replies View RelatedIn the menu via TOOLS-->OPTIONS-->VIEW I am trying to remove the scroll bars from just one worksheet but when unticked, this option affects all of the worksheets in my workbook. Is there a way to just nail this selection down to one sheet via properties?
View 2 Replies View Related1:
Does a code exist to do Shift Key_Arrow Right, Arrow Down and then select that range to Copy?
I know the arrows codes but was told by someone no code can ever exist for a Shift Key.
I need to select a range after a "Search For" but the Range is not determined by the actual Cell ranges.
My Code for Arrow keys are:
'Selection.Offset(0, 1).Select '*Right*
'Selection.Offset(0, -1).Select '*Left*
'Selection.Offset(-1, 0).Select '*Up*
'Selection.Offset(1, 0).Select '*Down*
'Selection.Offset(3, 0).Select '*Down * 3 Cells
2:
What is the macro code to insert more than 1 row ?
The code I use to insert 1 Row is :
Selection.EntireRow.Insert
Sometimes I need to insert 25 rows.
probably this is easy but i can not find it. i have created an userform and would like to add an arrow (<----->) on it to accentuate something. How can i add an simple arrow or line on a userform
View 3 Replies View RelatedIn the attached file and when I click on Name or ID (B5 or B6) the input msg will appear on the same cell using the validation rule.
Actually I need any input msg to appear in the other area where i put the arrow. I tried to move the input msg to that Irea and it worked great each time i click on B5 or B6 but after closing the file and opening it later.. they will go back to the same place.
Simulating the ALT+DOWN ARROW shortcut. I came up with the hereunder code.
View 4 Replies View RelatedI am trying to create the game of snake in VBA as practice, but I cannot find how to use the arrow keys as input. From what I have read it seems that it may not be possible, but how to change the value of a variable every time an arrow key is hit, post how to do so.
View 2 Replies View RelatedI have Sheet2 containing Supplier data in 2 columns - SupplierTAxCode and SupplierName (unsorted)
Sheet1 contains the Expenses section.
In Sheet1, column E6 to E1000, after inputting each SupplierTAxCode and pressing the RIGHT ARROW key, I would like to automatically get the SupplierName in column F6 to F1000 - from the corresponding data in Sheet2
In the event that the input in any of the cells in E6 to E1000 does not exist in SupplierTAxCode in Sheet2, would it be possible at the same time, to change the background color of the adjacent cell (SupplierName) in column F6 to F1000 at the same time - as a reminder to add those to the Supplier data in Sheet2 at a later date.
I could use Index Match and Conditional Formatting, but am concerned about the file sizes.
