Adjust Code From Cell Range Trigger To Button Trigger
Jul 1, 2014
I have the code below that is two separate activities and I want to change the second activity from a cell trigger (Set KeyCells = Range("K42:AD42")) to a button trigger. I need to first to remain unchanged.
I'd be ok if this was just one macro that I could assign to a button but because its two and I need to write the second's to clicking a button I'm over my head.
Its occured to me while writing this that because it'll be a range of buttons I'll probably need to make each one an individual code? Is this the case? If so I may have to just keep this as it is.
I'm building a Excel 2003 workbook that needs more than 3 conditional formats. I've tried the code given in the FAQ: Get Around Excels 3 Criteria Limit in Conditional Formatting but this only works on cells being modified directly. I've also tried the code in the post: VBA Conditional Formatting - Refresh when data changes but neither does the job I need.
I'm using a Format Control to present the user with a list of choices. When the control changes the target cell I want to apply a conditional format to that cell. This image shows the form. When the Format Control selection (in B2) is changed I want to apply conditional formating to the target cell (A2).
There will be up to 10 choices and therefore conditions.
inputed ...................... =A1+30 and date formated cell
i am trying to get the formula to tell me after the inputed date has been changed who the next tester is
i have tried using the IF command but it wont let me specify a date range ie 1 jan to 31 jan (or in the specified date codes) date range only one specific date with >= or <= which doesnt quite work properly
I have a set of data in a given range with one column indicating "priority" of 0, 1, 2, or 3 for each row. I would like to be able to allow users of the file to filter the data simply by changing the text of a given cell, or by choosing among filter options from a drop down. For example, I would label the control cell "show items up to priority:" and then let them enter 1, 2, or 3.
The "auto filter" option won't work because if they use the "sort" options in the drop down it will mess everything up. I would only want to use the auto-filter drop down if I could suppress the sorting options and the other filter options. (I'm after custom options of <4, <3, <2, <1).
Also, the advanced filter method doesn't seem to work well because users would apparently have to re-run the filter each time they want to change the criteria range value.
I've heard of macros that can be triggered based on changes to data in a given cell, but how is this done in this situation?
I guess programming a custom drop down box for selecting 0, 1, 2 or 3 would work just as well as having them type the number in. But I'm not sure how this could be done either.
Drop down list containing 5 stages of a typical sales cycle, i.e. client engaged, negotiation, prospect, Short-listed, Win/Close.
These are locate in column B and each cell in column B has a drop down list with these 5 options.
Now, based on which option is selected the corresponding cells in column C and D need to change. This change is defined as follows -
Cells in column C, need to change colour ( i.e. blue, orange or red)
Cells in column D, need to change to a percentage value (i.e. at intervals of 10%, 10%,20%, 30% ...etc to 100%)
Column C (corresponding cell i.e. if B3, implies change to C3 respectively). In this case cell colour needs to change to one of three colours listed above.
i.e. if cell in B2 has client engaged, C2 must show blue, Similarly if B2 has short-listed, C2 must show red.
Column D, Equally, depending on colour in Column C, the percentage must automatically populate.
0-30% = Blue 31-50% = Orange 51-100% = Red
I have used conditional formatting options on a trial and error for testing the above, but not getting much progress. I suspect due to the multiple changes and different simultaneous changes i.e. colour and text, a Macro is most likely needed? (I have limited knowledge of VBA).
In closing there are about 200 rows that need to have this capability. I.e. Contents of column B trigger respective cells in Column C and D.
I have a single workbook with five spreadsheets. My goal is to move data along a path (or work flow) from one sheet to the next by using a "trigger" pulldown menu choice.
Sheet1 = Prospects Sheet2 = New Sale Sheet3 = Upgrade Sale Sheet4 = Won Sheet5 = Lost Sheets 1, 2, and 3 use the same data layout for column A - K. Sheets 4 and 5 have the same A - K as above and also have columns L - R in common. The last column in sheet 1, 2 and 3 is a pulldown menu (New, Upgrade, Won, Lost).
Data rows on each sheet start at row 7.
The goal is to use the pulldown choice to remove the data from the current sheet (ex: Prospect) and add it to the next open row in the required sheet (ex: New or Upgrade). I also need to be able to make a similar move from New/Upgrade to Won or Lost.
I have an application that updates the text string in A1 every x minutes (time may vary). I'm trying to develop a toutine that will trigger a Macro/call a function/subroutine once only on each change of data (and then not trigger again until the next change...and must only trigger on change of data in this specific cell). So the timeline looks like this
start A1 = "Apples", Fire macro once
start + 10mins A1 = "Oranges", Fire Macro Once again
start + 25mins A1 = "Pears", Fire Macro Once again
Triggering the macro is no problem but constraining to one triggering is proving a problem (for me anyway) and is really holding up my application progress.
What I want to achive is the colouring of the cells in columns A-L using the trigger of the "Y" character in colums J-L
So when a Y is put in column J the cells to the left and including column J change to green. Then when a Y is put into column K the cells to the left and including K turn yellow. Finally when a Y is entered in column L the cells to the left and including L turn the lovely shade of Pink. It is possible the process will go from a Y in column J to a Y in column L mising out column K but I don't suppose this will matter.
I used to have it working in office 2003 to a fashion but have not yet got my head around 2010
I have a chart with 2 Y axis. I am attempting to write some code that will update both axis with the same max & min value that is triggered by the combobox selection. The code will update the axis but is not triggered by the combobox selection.
I want to trigger a macro that refreshes a pivot table but I only want to trigger the macro after 15 seconds. The reason is that I am pulling the source data from access mdb so I want only to refresh the data once the data is pulled.
I am having trouble trying to get an MS Access Query to run from MS Excel automatically.
I am trying to create an automated trigger in MS Excel that will automatically run my Access query by the times I specify in my statement.
For example: I need to run a query in Access at 9am, 12 pm and 10pm, that's it, but I believe I need to do it via Excel, I don't want the data returned to Excel, I just want Excel to execute the query at those specific times!
A1 is a drop-down list, created from Data> Validation>List, which lists 3 different words (Text1, Text2, Text3). I have recorded 3 macros (Macro1, Macro2, Macro3). Here's what I want to happen:
When Text1 is selected in A1, I want Macro1 to run (same for Text2/Macro2 and Text3/Macro3). I want the user to be able to change this value as many times as they wish and have the corresponding Macro run each time. I've tried creating the appropriate code in Editor using other threads on this forum, but I can't seem to figure it out.
I am encountering a strange situation with my Excel 2000. I have a public function, in a module in the VBA project associated with my workbook. But I'm not calling it from nowhere inside the code, or from other macros - it is not being referred anywhere in the workbook. Yet, after I make a slight change in code and not save my changes, when I return to the workbook and select a value from any cell with a validation-list (regardless of the sheet where it resides), that particular function is being executed!
Is it possible, that while running code that the code can say initiate the click event on a command button on another sheet.
Say that I have a button on Sheet1 called "wkscmd_DisplayDEI"
Behind that button is obviously some code. I want to know is it possible that while some code is running ( code does not reside on the module page for Sheet1 that it can send a pseudo click to the button?
i'm attempting to create a spreadsheet that will enable me to calculate the number of certain materials required to construct new rural fencing. I have 4 different 'types' of fencing each requiring different levels of materials required. For example, a Type 'A' fence, requires 5 'droppers' per 10m span of fencing, whereas a Type B requires only 1, a Type C also requires 5 - but a Type D does not require any.
Here is what I have attempted to generate so far - but is giving errors;
I have cut and paste some code provided by members of this great forum to insert a row in a second worksheet at the same row number when one is inserted in the active worksheet. e.g. If I insert a new line at Row 14 in worksheet "admin" I also get a new row at Row 14 in worksheet "report".
Code is as follows:
Private Sub Worksheet_Change(ByVal Target As Range) Set sourcebook = ThisWorkbook Set sourcesheet = sourcebook.Worksheets("admin") Set targetbook = ThisWorkbook Set targetsheet = targetbook.Worksheets("report") myRow = ActiveCell.Row targetsheet.Activate ActiveSheet.Rows(myRow).EntireRow.Insert sourcesheet.Activate End Sub
However, anything I do in worksheet "admin" triggers a new row to be inserted in "report".... If I change text in any cell, or make any changes at all, I get a new row in "report".
Is there a way to restrict this action to only a line insert?
I have a series of macros altering various workbooks and sheets. They're numbered Step1, Step2, etc. In my Step5 I have a series of Case statements in a macro, and I don't know how to get it to do what I want next. I have NOT tried running this code yet, and I'm sure I have something(s) wrong in it.
1) If the selection in DstWbk, sheet "Steps", is "01DSP" through "11DSP" the macro needs to delete specific columns in the SrcWbk, and then move to the next step (6).
2) If the selection in DstWbk, sheet "Steps", is "*DSP" the macro needs to go directly to the next step (6)
3) If the selection in DstWbk, sheet "Steps", is anything else the macro needs to flash a generic "No Data found" message and move on to Step7.
The part of the code that's throwing me begins at 'Select only the specific regional data' and ends at the "Case Else MsgBox"