On a userform I am using the MouseMove event to dropdown a combobox list. When I move the mouse away from the combobox the list remains dropped down. I want the list to close when the mouse moves away as it hides other controls on the form.
I have tried using MouseMove when over the userform or transparent labels around the combobox to set focus to other controls but this does not close the combobox list. The only way to close the list is to click away from the combobox. I have looked at how to simulate a mouse click but that is beyond my limited understanding of vba. If this is the only way to achieve the required result then I need some patient assistance. I am using Excel 2000 on XP.
I have a menu structure in place so when a user clicks a button, it opens a Userform as a menu. The only problem i have is that if the user doesnt want to use that menu, and clicks on the spreadsheet, it doesnt dissapear.
Is there any VBA code that anyone knows that when a user clicks on the spreadsheet, or even just clicks their mouse anywhere (ie other menus at the top, spreadsheet, desktop) like a mouseout code on HTML so the menu will dissapear?
I have a problem here regarding the combo box. The whole Column A has combobox. It would be easier if by just hitting the TAB or Enter it would go to the next cell just like what is usual in excel.
TAB = next cell to the right Enter = Next cell below
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.
I would like to call upon a function until a certain criterion is fullfilled. Then, I would like to have the result returned to me and exit (all) open functions. For value1 = 1 and value2 = 10 I expect value1*value2 = 100. Instead, the routine returnz zero. What is the logical flaw in the code below.
Function testfunction(value1, value2) If value1 = value2 Then 'Calculating the difference testfunction = value1 * value2 Exit Function ElseIf value1 < value2 Then value1 = value1 + 1 Call testfunction(matrix1, matrix2) End If End Function
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.
I'm trying to do a few things actually. I am somewhat a newb at programming, although I do understand how C++ and the coding works within Excel. To me, it's like the English language...knowing all of the vocabulary and formatting it right is important. If I don't know the vocabulary, I don't know what options I have.
This tool is being used in the call center I work in. It is to help eliminate repititious information used to notate accounts. I have attached a picture of what the tool looks like so you can have a better idea of what I'm working with. Here are a list of things I'm trying to do which I have searched and not found answers to:
1. I am trying to use a middle mouse scroll to scroll through validation list. This list information is located on a different locked tab. Basically, I have a few rows where a drop down selection can be made and it would be easier to scroll rather than manually moving the slider within the validation list.
2. As you can see, this "tool" an employee and I have created has many buttons. What I would like to do is to be able to click a button on the right and have it "stack" text within a single cell. In other words, if I click the button once, the text may be entered into the "actions taken" cell. If I click another button, I can have the option (or maybe a little "+" next to the button) to add this button's information to the end of the information already entered within the "actions taken" box. I've tried recording and using the keyboard and selecting "end" and then having the text added, but it didn't work. I've seen "loop" VBA code, but I don't want something to repeat, I want it to stack in the cell when clicked manually.
I'm sure there is a way to make the tool much better using VBA code and not use Excel, but I don't have an extensive knowledge of Excel to do something like that. I'm a very fast learner and understand how the coding works, so if you have an idea which may help, and you understand what I'm trying to do here, please provide your .02.
Basically, it's used so the Customer's name and phone number are entered manually, and then just about everything else is automated by the use of the buttons on the right. If I could get the 2 above options to work, it'd be perfect. I just figured out I could eliminate screen flicker and hide the macros working by using:
i'm writing a refrigeration selection user interface, working from values on an excel spreadsheet. how to get the programme to automatically select a value from a list or range once a user has selected corresponding value from a list within a combobox. for example if a user sets the temperature of their refrigerator to -5 celsius i need the programme to automatically select the corresponding value of enthalpy for the air at that temperature.
I have a userform where I have 2 comboboxes. The first combobox shows the the first column (only 1 of each) and the second comboBox shows me the secondary list that correlates to the valuse in the first from column B. Now I have a text box that I am trying to get the value from column C depending on what I have in the first 2 comboboxes. What is the easiest way to do it? This is all in VB since it is a UserForm, and using Vlookup seems to be too many lines if I go that route. Is there a way to use Index and Match in VB where it would be more efficient? I attached just a sample of how the data would be layed out in the Excel sheet.
Looking for a way to use VBA to get a list of the tab names from a closed workbook and put them in a column of the worksheet that has the macro. Is there a good way to do that?
Update a list using a Combo Box works fine when the list is on the same worksheet but when the list is on a different Worksheet it does not work.
Is this achievable? I guess so! but was wondering how to do it.
e.g: if my range defined name called let us say "SP" with a range AD1:AD60 in Sheet1 when I use a combo box the Listfillrange will contain: SP
When inserting a new entry using the combobox it works fine but when the same defined name range points to a different Worksheet example: Sheet2 then the update does not work although the ListFillRange contains the same Range name: SP.
I have a spreadsheet which shows information based on what a person has picked and how long it took them to pick.
What I now want to show is an extract which will show - in a separate area - what shift & person still has not completed the pick (completed time section remains blank) together with the type of product they are picking plus the 'operation number'.
My current spreadsheet is as follows;
[IMG]C:Documents and Settingschristine.lawsonDesktop[/IMG]
Table shows as follows:
SHIFT PRODUCT TYPE MSN OP NBR PICKER NAME DATE PICKED START TIME COMPLETED TIME OVERALL TIME TAKEN AVG LINES PER HR
BLUE T&F 4 001 FRED 6/11/12 17:55 18:40 00:45:00 4
[Code] ........
As you will see from the attached picture/table there are 2 lines which do not have 'completed' times shown. It is lines, such as these that I need to be able to show in a separate area.
It’s been working perfectly up until around an hour ago. However, now when I close Excel, I get compile errors.
These compile errors seem to be because Excel is trying to access controls in the workbook after it has closed. Since the workbook is closed, VBA can no longer ‘see’ the controls, and therefore it thows up errors.
I’m also getting a similar error on a Worksheets("DataExplorer").unprotect line, which seems to be because the worksheet isn’t there after closing.
These errors only occur when I close the whole excel application using the big cross in the top right. If I just close the workbook (using the smaller cross just below the big cross in the top right of excel), it doesn’t throw up these errors.
Just to clarify: all of the code runs perfectly when Excel is open. The errors are being thrown up for lines of code which run without problem until Excel is closed.
I am trying to filter a list for a ComboBox using VB. I have the following
Defined Name 'MemMapSel' 1stCol 2ndCol 3rdCol 4thCol 5thCol Alarms a BOOL 2001 4000 Commands i BOOL 4001 5000 Status s INT 7001 8000 Free x DINT 9001 10000 etc etc
I am using a combobox to select which row i require and am mapping the 4thCol to a cell At present the property 'ListFillRange' is set to 'MemMapSel' displaying all 5 columns and bounding column 4 as the value
What i really need is to Filter this list, so that the combobox only displays the BOOL entries in the 3rdCol, or the INT, etc I think i can use the Select function to do this, but i am not sure how to go about it.
Trying to get the list from a combobox in an online form. I'm doing this as a check to make sure what I need to be there is there. Then the macro will select what I need and move on. If not found in the list, then it will move on...
So I start thinking about it and I'm not quite sure how to do that in Forms. Does ListIndex get me there?
I'm unable to give the site because you'd have to login with a password, but here's the code I have to Set the combobox:
Set PartNumOffr0EDrop = .Document.all.Item("PartNumOffr0EDrop") myVal = PartNumOffr0EDrop.ListIndex
I need to know how to loop through the combobox and store the value of each member of the list to a variable or cell.
In VB is there a way to check the values in a combo box, to check whether the value you are trying to insert into it exists in the list, if not add it to the list.
I have 2 columns of data on Sheet2. The first is Product in column B. The second is Benefit in column C.
I need to look at product column B on Sheet2 and add unique items to my first Combobox after the command Userform1.show.
The person using the form will select a product from Combobox1 and then Combobox2 should add the corresponding Benefits from the benefits column on Sheet2.
As a side note: I expect products and benefits will continue to be added to columns B and C in the future so I can't have a "hard-coded" set of rows if that makes sense.
I've pasted a small 2 product example of Sheet2 below.
ProductBenefit Account DefenderLoss of Life Account DefenderDisability Account DefenderInvoluntary Unemployment Account DefenderLeave of Absence Account SecurityLoss of Life Account SecurityDisability Account SecurityInvoluntary Unemployment Account SecurityLeave of Absence Account SecurityHospitalization Account SecurityNursing Home
I have built a Userform that is for a Liquor Inventory. I would like to scan each bottle's UPC Code and pull it from a range list that is set up in (Combobox "UpcBarCodeBox"). That information is stored on a sheet "Liquor & Wine Inventory" in Column "A" (Unsorted). Column "B" is the name of the liquor which is also the column that everything is sorted in alphabetical order. What I would like to do is populate several textbox's that I have set up so that I can input missing data or data that needs to be updated. The problem is I would LOVE to keep the list sorted by Liquor Names (Column "B") and not by UPC Bar Codes (Column "A"). From my research I have found that Index and Match might be the way to go but I can't seem to get it to work right. Since I am still learning VBA, Some things still escape my grasp.
VB:
Private Sub UserForm_Initialize() Me.UpcBarCodeBox.List = Worksheets("Liquor & Wine Inventory").Range("A5:A205").Value With ComboBox2
I'm trying to programmatically set the value of a combobox but I'm running into errors if the combobox list does not already contain the value I'm trying to set it to. How can I first test if the combobox has that as a valid option and, if not, add that item?
i found this example of a combobox at http://msdn.microsoft.com/en-us/library/ms996411.aspx. but I don't think it is for excel. Is there anyway I can use code to make it so my combobox's list will be like the picture so it shows all the words instead of cutting them off?'
I have this code that works in Word but when I try it in Excel I get a "runtime error 438/object does not support this property or method" on this line: "For Each ish In Sheets("SDF & ER").InlineShapes"
Code: Sub LoadPEGC() Dim ish As InlineShape Dim cbo As MSForms.ComboBox Dim xlapp As Object Dim xlbk As Object
I have created a userform where I have a combo box and an options box. I want the combo box to reference a specific list of values. And if the check box is selected, I want the same combo box to reference a different list of values. This is the present syntax I am using. For some reason, The value of the check box does not alter the list in my macro.
Private Sub Userform_Initialize() If chkHeader.Value = True Then cmbSort1.Clear With cmbSort1 .AddItem Range("A1") .AddItem Range("B1") .AddItem Range("C1")
I have built a Userform that is for a Liquor Inventory. I would like to scan each bottle's UPC Code and pull it from a range list that is set up in (Combobox "UpcCodeBox"). That information is stored on a sheet "Liquor & Wine Inventory" in Column "A" (Unsorted). Column "B" is the name of the liquor which is also the column that everything is sorted in alphabetical order. What I would like to do is populate several textbox's that I have set up so that I can input missing data or data that needs to be updated. The problem is I would LOVE to keep the list sorted by Liquor Names (Column "B") and not by UPC Bar Codes (Column "A"). From my research I have found that Index and Match might be the way to go but I can't seem to get it to work right.
Code:
Private Sub UserForm_Initialize() Me.UpcBarCodeBox.List = Worksheets("Liquor & Wine Inventory").Range("A5:A205").Value With ComboBox2 .AddItem "0" .AddItem "1" .AddItem "2" .AddItem "3"
