I have set up a combolist box, and wish to run different VBA subroutines depending on what the user has selected from the combolist. How do I do this? Apologies in advance if this sounds like a silly question.
View 2 RepliesWhen I make a comboBox selection from the dropdown menu, the menu stays down until the last statement of the comboBox code is finished. How do I make the dropdown disappear immediately after the selection is made?
View 9 Replies View RelatedI attached a sample spreadsheet but I was wondering if there was a code that could display a range based on an option selected in a dropdown box.
excel help.xlsm
I am using this code to hide or unhide rows of text on another sheet:
VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)
Dim sAddress As String
Dim sValue As String
'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)
[Code]....
When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.
The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..
Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)
Code solution can be entered directly beneath:
VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True
I want to be able to run a certain chunk of code in the macro if a certain condition is true, or if that condition is not true then I want to bypass that chunk of code. It doesn't matter if this is accomplished with an if... then... else statement, or some other way.
View 6 Replies View RelatedI have a spread sheet where i have created a drop box in Cell i4. There are 56 choices in the drop box. These choices come from cells B160:B215.
There are dollar values in cells C160:C215 correspond with B160:B215. I am trying to find a formula for G16 that will look at i4 and find the corresponding dollar value from C160:C215.
I have a group box with 5 little round option buttons in, what i wanna do is connect the options to an image and when an option is selected the image that corresponds to it shows, then when a different option is picked another image relating to that option shows.
Select option 1, image 1 is shown
select option 2, image 2 is shown
What I am trying to accomplish is the ability to insert a value into a cell if an option button is selected. If the option button is not selected I want there to be a different value in the cell. This is what I have so far:
Sub OptionButton2_Click() Range("F8").Value = "$299" End Sub
What I am having issues with is the "else" clause. I want the value to show $0 if the option button is not selected. I attached a screen shot for reference.
I have a TextBox and a ComboBox on a worksheet, is it possible to hide them with option buttons?. So only one box shows at a time.
Option Button1 show TextBox
Option Button2 show ComboBox
If I have a dropdown with
A (value 1)
B (value 2)
C (value 3)
D (value 4)
and a person selects one of those letters in the dropdown in column 1, I would like column 2 to show the number value associated with it
I was trying to have the the mouse focused on to the textbox when an option button is selected, but it seems not working.
[Code]....
I'm creating an excel spreadsheet that will be sent out to a few users to enter data into. In this spreadsheet, multiple columns contain drop down lists created using the Data Validation tool. The lists are referencing named ranges on a second worksheet.
In these drop down lists are terms that consists of one or two words. What I'm trying to do is when a user goes to select an option from the drop down list, I want the matching one letter code to be entered into the cell instead. For example,
Column AK has drop down list with following options:
Good
Moderate
Poor
When a user selects one of the options, I want one the following respective letter to appear instead......
I have a survey with different groups of Control Toolbox options buttons on it. I want to ensure that each question has an option button selected before the survey can be exited and emailed onwards. The grouped button names are: GroupA, Group1 through to Group6.
View 2 Replies View RelatedI have a large macro called SplitData, which takes a load of data and splits it and reformats it each month.
I have 4 Activex Control combo boxes on one sheet that are linked to a range...this range changes each month, so I blank out the range and then repopulate.
My problem is that when I blank out the range, it triggers the Private Sub ComboBox1_Change() and throws some errors because there is no data to select.
So, I was wondering if there is something I can set in my SpltData macro that will not run the ComboBox1_Change sub while the SplitData macro is running?
Just started using Macros on Excel.
I recorded a Macro that would take a value of a particular cell and add that value to the selected cell in the same row. However, this macro just runs on one cell and I want it to run on the entire row.
This may seem like a trivial issue but I've been racking my brain for the last couple of days to figure it out.
Here is the VBA code of the Macro I recorded. It adds the cell value to another cell with formulas already in it as you will see below:
Sub SpreadingTest15()
'
' SpreadingTest15 Macro
'
'
ActiveCell.FormulaR1C1 = _
"=IF(MarRF!RC9=""Trade"",IFERROR(IF(RC12=""BL"",RC13/12,INDEX('Variable Data'!R4C2:R16C17,MATCH('1410-Rev'!RC14,'Variable Data'!R4C2:R16C2,0),MATCH('1410-Rev'!R14C,'Variable Data'!R4C2:R4C17,0))*RC13),0),IF(RC14=R14C,RC13,0))+(RC35)"
ActiveCell.Select
End Sub
Using macro's on Excel 2010. What I'm trying to do is create a macro that will copy a range of cells from one sheet to another depending on a option being selected from a drop down box. I've tried to use formulas but without success.
sheet 1 contains a list of approx 20 people with rows containing sales figures per week.
Is it possible to have a macro that will copy the rows to sheet2 depending on the dropdown? The drop down has already been setup with people's names
E.g.
if sheet 1, cell a1 (with data validation setup) dropdown contains "mr smith", copy sheet2 row A1:A9 to sheet 1 cell a2
or
if sheet 1, cell a1 dropdown contains "mr cooper", copy sheet2 row B1:B9 to sheet1 cell a2
etc... for each name in the dropdown
The idea is so that i select a dropdown and it copies the sales figures that match the dropdown name, if i then select another dropdown, the corresponding figures are copied to the same place.
I have figured out how to write a macro to make charts for me automatically, but it only runs the macro on the exact same range every time.
I have about 100 different students to make charts for and would really like to find a way to do it more automatically
Is there a way to write a Macro so that I can run the same steps on a different range (same number of rows and columns)?
Edit: having learned from my first post, I am adding more information.
the first chart is from the data in the range from B5-H7
the next chart would be from the data in the range from B11-H13
The charts are not evenly spaced from each other. I will need to select the range (same size) each time.
I can record the Macro, I just can't apply it where ever I want to.
Basically, I have a sheet that shows time in Days going downwards (monday tuesday etc.) and is broken out by week. In Column C, I have Project Names. Columns G-O have numbers.
What I need to do is run a custom sort multiple times down the page so that each week has been sorted by Project Name (column C). I was hoping to be to just make a quick macro shortcut to run my custom sort and each new range I selected but I don't know how to make the macro run on the actively selected cells and not hard-coded cells.
I am dealing with several very large spreadsheets using VBA to do various things. I found that my code worked well, but was taking a long time to run. The biggest time consumer was my use of the AutoFilter features. I have since turned calculations to manual before my code runs and set it back to auto when my code is done running. What are the potential consequences of my turning calculations to manual and then back to auto?
View 2 Replies View RelatedDoes any one know how I can resolve the calculation error that shows up in a pivot table when you select "show data as a running total in" option and you end up with a very small difference error.
In other words if I add 12.96 + 2.04 + (-15) and you get 1.455E-11 instead of zero. When these calculation errors show up in normal spreadsheet calculations outside of pivot table you can always use the ROUND function to correct these arbitrary calculation errors.
I'm looking for a recommendion for handling this in a pivot table where I have a couple of hundred columns of data that are using the "running total in" option.
I have a userform with a combobox in it that's rowsource is a column of company names in a worksheet. When a company name is chosen, a combobox change private sub runs and many userform textboxes are populated with information about the company that was chosen from the combobox dropdown. This information is stored on a worksheet. I then want to edit any of the information in these textboxes. Once my edits are made I have a CommandButton that is pressed to save the edit changes. This CommandButton runs a private sub that disables the combobox (thinking this would prevent the combobox private sub from running), deletes the row that the information originated from, and then SHOULD make a new row of values based on the contents of the textboxes following the edits. The problem is that the CommandButton coding that deletes the row causes the combobox change private sub to run because the company that had been selected is now the missing from the rowsource; this causes an error.
View 8 Replies View Relatedis there any way to get the value for the selected item from the combo box in vba code?
View 5 Replies View RelatedI need to populate two combo boxes from excel sheet, the data will be like below:
Column A Column B
A 1
A 2
A 3
A 4
A 5
B 100
B 101
B 102
So from the above data, one combo box should hold unique values A & B.
On selecting a value from the 1st combo box A or B, respective values should be populated in 2nd combo box.
So the data should be like below:
If A is selected in the 1st combo box, then 2nd combo box should only show the values 1,2,3,4 & 5.
If B is selected in the 1st combo box, then 2nd combo box should only show the values 100,101 & 102.
Friends I need it in a macro and one important point is, this is dynamic and it is not static and the data can be more.
I've created and coded a vba userform that creates purchase orders for my projects. The user begins by selecting the project code from the combobox (the project code is a unique identifier of each project). Once the purchase order is created, the information is logged in a separate sheet called "POLog" and the userform is cleared. The project code is saved in the first column of the "POLog".
My problem is that when I have more than one purchase order to create for the same project (sometimes I have 20 or 30), the combobox starts out empty and I have to manually select the project code from the combobox. Is there a way to allow the userform to recall the last project code that was used? Maybe recalling it from the last row in the "POLog" sheet?
Its a phone number directory. the data is retrived based on nickname. when a nickname is selected, its phone number and details will be updated in the textboxes ... example: tony (in A2) is selected from the combobox, his phone number (in B2) and details (in C2) are updated in the textboxes.
View 2 Replies View RelatedI know you can set cells equal to other cells, but can you set the cell equal to a combobox created in VBA.
View 2 Replies View RelatedI have a combobox with populated values from a named range. I need the first value to be always selected when the form is opened - is there a way to do this
View 7 Replies View Relatedtry and achive is when a user selects a item from the 1st Combobox the 2nd Combobox is the populated with the cell that is to the right of the selected item.
for Example if a user selects AAB from combobox1,, Combobox2 should populate with Belly.
Maby using combobox1_Exit for the excercise.
I have a table, headers "FirstName" and "SurName".
Further a Userform with 2 Comboboxes "FirstName" and "SurName"
I'd like to choose the FirstName (say Jack) in the "FirstName" combobox, and based on that get the choice of the Surnames of all my Jacks in the "SurName" combobox.
Actually my sheet has much more fields and comboboxes, but i think my problem is just that I do not find a way to populate them dynamically.
How can i Loop through a combobox's values and compare to a string value and then set the listindex of the combobox to that value?
View 5 Replies View Related