I have a combo box that insert certain information in the row corresponding to the active cell. Apparently the events that trigger the code are generated by the combo box ( change or on click) but pressing any key also triggers the events of the combo box.(this is undesirable)
The following code is excuted even when any key is pressed. Any suggestions on how I can go around this problem? (get this code executed ONLY when a combo box event is triggered)
Private Sub cmbLinkInfo_Click()
'The record is inserted only if the user is inside certain range
'The range is inside the affected links table and between the columns C and F
If (ActiveCell.Row > 25) And _
(ActiveCell.Column > 2) And _
(ActiveCell.Column < 7) Then
Cells(ActiveCell.Row, 3).Value = cmbLinkInfo.List(cmbLinkInfo.ListIndex, 0)
Cells(ActiveCell.Row, 4).Value = cmbLinkInfo.List(cmbLinkInfo.ListIndex, 1)
Cells(ActiveCell.Row, 5).Value = cmbLinkInfo.List(cmbLinkInfo.ListIndex, 2)
Cells(ActiveCell.Row, 6).Value = cmbLinkInfo.List(cmbLinkInfo.ListIndex, 3)
Is there an event that fires every time anything is clicked within a userform? I have seen the userform_Click() event however, this only works if the userform is clicked directly and not if something like an image is clicked on top of it.
I wrote some code to validate an inputfield. I want to keep the focus on this field until a correct (numeric) value is inputed. Therefore I use teh beforeUpdate event instead of the afterUpdate event. Some strange things happen (see code below):
1. When I press enter after putting some non-numeric data in the inpBedrag2 field the msgbox is displayed three times in a row. This doesn't happen when the field loses focus by clicking another field (-> msgbox only displays once = correct behaviour) 2. What's really driving me nuts is the fact that the (more or lesse the same sub) works perfectly.
What am I doing wrong? I'm not getting it. Probably I'm doing something very stupid ... but I can't figure it out myself.
So, I have a worksheet with a bunch of combo boxes. The code for the worksheet is below. The issue I'm having is that anytime I drag and drop a cell anywhere on the page, every single control on the worksheet triggers. VB runs through all the code on the sheet, TWICE(I used the debugger extensively trying to find a solution), and causes dozens of successive re-calculations of the entire workbook, which has a couple of tables. This happens on any cell drag-drop on the worksheet, or when I click the command button on the screen.
I have no clue what is causing this. Even more bizarre, it seems to trigger a custom function which is located in a separate module, and isn't even utilized on the worksheet in question. I guess this is because it makes the whole workbook re-calculate?
I need a userform textbox event that fires after I tab or click out of the textbox. Going by the list of options:Beforedragover, BeforeDroporPaste, Change, DblClick, DropButtonClick, Error, Keydown, Keypress, keyup, mousedown, mousemove, mouseup.
I can't figure out which one will do what I want. The change event happens instantaneously which doesn't work. I need to fire off the event when my focus leaves the textbox.
i sell solar panel systems and i've broken it down to 18 different packs that i can sell, the reason for this is that i can very quickly have a tailored quote for a particular client. what i want to do is is literally from a pull down menu have the 18 packs, pick one and for all elements of that quote to come up. i have used a data validation list for the pull down menu which works fine. for the 18 packs, each one has an array of info of between 15 - 18 rows and 4 columns wide (containg info like part description in first column, then cost in second column, quantity in third column and total cost in fourth. how i set it up was, i have all the seperate packs going for left to right on a spread sheet with the title name on top. i have created a quote area so when i use the pul down menu to get title all that pack info should come up. i have done this by using the hlookup command to return the first column on the left - description on parts and this works fine - then to return the cost in the second column, i am using a vlookup command by looking for the description on the left to return the cost. this works for 90% of the info and for the other 10% i get an #N/A when i know it shouldn't. all cells have the same format so i know this isn't the problem. i would really appreciate any help on this from anyone who has had this problem before.
I am facing an issue related to IF formula. It does show the correct result in one and another cell, but in a few others - doesn't.
This is strange since all variables are correct, and parameters are the same for every cell. And, in theory every cell should show the right answer.
In addition, the result doesn't change even when changing variables in cells that are dependants to cell where IF is located.
Concerning the formula itself. Variables are as such:
- different container types (20, 40, 40rf, 45cs, 45rf, 45) - different weight limitations per container type (e.g. 20 container max weight 26t) - using IF(OR(AND(.... - if any of the conditions met (e.g. 20 container < 26t ) then VLOOKUP for the rate from data based on the place name. - if non of conditions met, then show "Overweight cargo"
I have a workbook where it is important that users do not delete any information in a particular range. But, I do not want to 100% block users from deleting the info.
What I would like to do is, if a user selects a cell within the range, and tries to overwrite or delete, that a warning box comes up to advise the user against changing the data. Is there a way to do this?
I've been trying to figure this out all morning, and it's giving me a headache. I'm trying to write some code in the WorkBook_BeforeClose module as shown below:
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim ans As Integer ans = MsgBox(prompt:="Do you want to save the changes to " & ThisWorkbook. Name, _ Buttons:=vbInformation + vbYesNoCancel, Title:="X2O")
Select Case ans Case Is = vbCancel Cancel = True Exit Sub Case Is = vbYes...............
As you can see, I show a message asking the user if they want to save the workbook or not. Clicking the Cancel and the Yes buttons work fine. However, the No button causes the message to be shown again. If I then click No the second time, the workbook closes!
I have a program that uses the Document Open event to display a custom form. This program is being used on about 50 computers for the past 8 years with no problems. One user has a problem now. They can open the program once and the code fires. But when the user tries to open the same file a second time, the "Microsoft Excel has encountered a problem and needs to close" dialog box is displayed. The document that is recovered has no vba modules and no code in the Document open event. I've uninstalled and reinstalled Office Professional. Shut down all firewalls.
Is there a way to write a Worksheet_SelectionChange (ByVal Target As Range) event in module after creating a sheet in VBA? I constantly delete a sheet, then repopulate it with a new one that is empty, but I need to add some code that happens if they should change a particular cell. It worked when I ran it on a worksheet without refreshing, but as soon as I cleared and repopulated the sheet, it was gone. Is there a way to preserve this?
I am having a few problems trying to amend the code so that it only uses the text box and combo box below. Unfortunately I am having a few problems. I am new and have amended this spreadsheet / code from another user on this site. When I delete the combo box on the right, using developer mode, a run time error occurs.
I've got a worksheet which uses comboboxes at certain points for users to input data. At other points in the sheet they are entering directly into cells.
I've got the sheet protected, with the input cells unlocked so that it is possible to jump to them using the tab key.
However, I can't seem to make the tab key jump to any of the comboboxes. Ideally I'd like to set the full order for the tab key so that it jumps between input cells and comboboxes at the required stage.
I have a problem where a combo box in a form won't populate from the VBA code in the initialise routine. I've tried a couple of difference methods for populating the combo box without success. The code I have is:
VB: Option Explicit Sub NewTender() Application.ScreenUpdating = False
I tried the excel file uploaded by Norie but it only displayed "Is". Are there any settings I need to modify to allow the add item property to work?
I have 8 combo boxes on Sheet 1, which must contain the info from Sheet 2. The "Group" Combo boxes should contain the letter of the group (A, B, C or D - the information should be used from the D2:D5 cells from Sheet 2).
I want when user clicks on the Group Combo box (the one on the "1st Grade" row for example), and select some group (for example A), in the left combo box (which at first says "Choose Group ..."), all the names of studenst from 1st Grade and A group to be displayed.