how to alter this Private Sub for my code to execute automatically once all 3 cells have changed. At the moment it executes as soon as any of the 3 cells change.
Here's the Private and Public Sub codes:
I have some rather complex formulas that when a value is placed in one of any three cells it solves for the other two, taking values from other places on a worksheet (including the cell itself).
Scenario: Cell F10, G10, and H10 all have formulas in them. If a value is placed in F10 it solves for G10 and H10. If a value is place in G10 it solves for F10 and H10. If a value is placed in H10, it solves for F10 and G10.
I really do not think the actual formulas matters at this time...
Has anyone done this before, using VBA code to place the formulas in cells F10 G10 and H10? Look for a working example of this if possible.
I have the following code pasted into the worksheet module which used to work fine but no longer does. I didn't touch the code, it just stopped working.
Private Sub WorkSheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$A$16"
Select Case Target
Case "Custom Color 1": Call CustomColorInput1
Case "Custom Color 2": Call CustomColorInput1
Case "Custom Color 3": Call CustomColorInput1
Case "Custom Color 4": Call CustomColorInput1
Case Else:
End Select
Case "$A$17"
Select Case Target
Case "Custom Color 1": Call CustomColorInput2
Case "Custom Color 2": Call CustomColorInput2
Case "Custom Color 3": Call CustomColorInput2
Case "Custom Color 4": Call CustomColorInput2
Case Else:
End Select
Case "$A$18"
CustomColorInput 1-6 are the same except they input data into different cells F16-F21.
When I attach any of the CustomColorInput macros to a button it works fine. When I try to call it from the worksheet module, the data input box pops up but the data doesn't get entered into the cell. When I try to step through the CustomColorInput macros using F8, the input box pops up, I enter data and press ok. Then I continue with F8 and the debugger jumps to a custom function that I entered (below), which is not called for and is in no way related to the code.
Function PullAfterLast(rCell As Range, strLast As String)
PullAfterLast = Mid(rCell, InStrRev(rCell, strLast) + 1, 256)
End Function
When I close VBA, the data that I typed into the data input box is in the correct cell.
So basically, I'm getting three different responses from the offending code depending on how I call it up. Can anyone tell me what's going on?
More generally, in this and previous occasions, I've had problems with macros that cease to work for no reason that is apparent to me. What are some things that would cause a macro to stop working, without actually changing the code (other than the obvious, like referring to names or worksheets etc. that don't exist anymore)?
I have a workbook that has two worksheets. One worksheet is an input worksheet. A user will select a date from a drop-down list and type in the events that occurred on that date in 8 adjacent cells. The user selects a button that advances date and clears form. On sheet 2, whatever was typed into sheet one is saved via VLOOKUP formula. However, if I try to change something retroactively and select a previous date from the drop-down list, it clears everything in sheet 2 that was typed for any selected day.
View 1 Replies View RelatedColouring calendar entries based on a date match... VBA to colour a cell on each line based on date match
The code works fine if you change the date in the cell and press enter however my date changes are triggered via a Vlookup which the code doesn't recognise has changed so doesn't trigger the macro.
Is there any way to amend the VBA code shown below so that it triggers the macro when dates are changed in column I via Vlookup?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Target.EntireRow.Interior.ColorIndex = xlNone
[Code] ......
I have a UDF that goes like this:
Function SumIfPurple(inputRange As Range, _
answerRange1 As Range, _
answerRange2 As Range) As Variant
Dim SumAnswer As Variant
'If inputRange is turned purple then it is equal to anserRange1, if it is left with no fill it is equal to answerrange2.
If inputRange.Interior.ColorIndex = 39 Then
SumAnswer = answerRange1.Value
Else
SumAnswer = answerRange2.Value
End If
SumIfPurple = SumAnswer
End Function
I would like to have a sub that will have the formula automatically calculate when the inputRange's color is changed.
There is a column in my spreadsheet which needs to reflect a " percent done".
I used to enter just a numerical value in (eg. 20) to reflect 20% done. My boss wanted it to show as an actual percentage (eg. 20%). I changed the format to Percent, Zero decimal places. Then you had to enter .20 to get the 20% to show.
Now my boss doesn't like that - and wants to be able to enter "20" to get the 20% to show. I put the following code in my SelectionChange event...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'other logic
If Target.Column = Asc(PercentDoneColumn) - 64 Then
If Target.Value <> "" Then
Target.Value = Target.Value / 100
End If
End If
'end other logic
End Sub
My problem is that this works well when putting a new percentage in, but not for when you click on a cell that already has a percentage in it. For example, if you have .2 (displaying "20%" in the cell), and click on that cell, it divides that number by 100, and gives you 0.2%, displaying as "0%". I want to enter a number between 0 and 100, have it displayed and stored as a percent. At this point, I'd settle for the displayed, but would prefer to have it stored as a percent, too.
I also want it to not reset my value unless I'm putting a new value in!
I'm trying to code so that any changes made to Excel document will automatically change in PowerPoint. I have Copied and Pasted Special link so that its connected but when ever a change is made in Excel in powerpoint I get a pop up box saying I need to press update. But I want to bypass the pop up update box and have it so that powerpoint automatically updates when I change something in Excel.
This is the code I have so far. I don't know if it's right or if I am on the right track as I get a compile error saying I need a End with.
[Code] .....
We have 3 PCs, all running MS Office 2013. On 1 of these machines, it is doing strange things with formatting. If you open a document or try to paste anything into certain documents, it decides everything is currency format and assigns all sorts of wrong formatting to the entire sheet, or the entire document. There may be some cells in the doc that are indeed currency, but only a small proprtion. If I open a new, fresh document and paste into that document, it does not do this, it seems to work normally, only applying currency formatting where it might be applicable. On some larger docs that have this issue, no matter what I do, it just continues to apply these strange settings.
View 2 Replies View RelatedI am very new to vba and trying to figure out an auto save macro that will automatically save my workbook when a certian cell range is changed. Right now I have a macro set that automactially record the time and date of when a change is made to the name cell, I want to set up a macro that will automatically save the file when the time is updated.
This program is used by several users and they have a tendnecy to forget to save the program so that when other people want to check the updated data nothing has changed because the changes have not been saved. I have attached the file that I am working on. When a change is made in column F then Column G automatically updates, now I want column G to trigger autosave. I would also like a msgbox to appear to tell user that file has been saved.
I am looking to have a macro which will automatically add the username and date to a cell, when the cell above changes (or in this case =x). I have a bit of code from a previous project, but I have changed Target.Row to Target.Column and it is not behaving as I require:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B2:BZ2"), Target) Is Nothing Then
Range("C" & Target.Column).Value = Format(Date, "dd-mmm") & " " & Format(Time, "hh:mm") & " by " & (Application.UserName)
End If
End Sub
See the attachment for the example. What I need is whenever row 3 ="x" I need the corresponding cell in row "d" to update with the latest date, time and username. Please not the macro is currently not active on the sheet as i've added the ' character.
1.I need to protect certain locked cells from editing and allow certain unlocked cells to be changed on multiple worksheets.
2.When all of the changes are made to the unlocked cells, I need to password protect the entire workbook (except one worksheet) from any changes. (i.e. Prevent even the unlocked cells from being edited)
3.I also need a password to un-protect the workbook and return it to the state described in # 1. above .
I have created a pivot table that is connected to an input sheet with data. The input sheet retrieves data automatically from a external source through an add-in to Excel. When updating data the fields expands, but only for the items which have been changed. I want the table to be updated automatically, but not the fields expand automatically. Is there any pivot options to prevent this problem?
It should be mentioned that the pivot table is not directly connected to the input sheet (which is updated from the external source), but from a "help-sheet" reflecting the input sheet with some additional columns. I use conditional formatting and name range in the pivot.
I have a list of diagnostic procedures, some of which are for the same patient. I need a macro that does the following: move every second procedure (a couple of cells per row) of one patient number (column B) next to (11 cells to the right and 1 row up) the first procedure. But only if there's less than 3 months between the procedures (dates are in column G) and if both procedures were on the same side of the body (left or right, column L)
Is there a macro that can do this for me?
Is there a macro that just moves a couple of cells in a row 11 cells to the right and 1 row up?
I am trying to have some automation to my WS and what I want to do, is if a certain column (column 104) is changed, it will fill out that column with the data entered as long as the account is the same (column 5). Here is the code I tried, but realized very quickly, it puts me into a loop. This is being called by a change worksheet fuction
If Target.Column = 104 Then
If IsEmpty(Target) Then
Else
testacct = Cells(Target.Row, 5)
temptest = testacct
s_TempSwitch = Target.Value
Testoffset = 0
Do While temptest = testacct
temptest = Cells(Target.Row + Testoffset, 5)
Cells(Target.Row + Testoffset, 104).Value = s_TempSwitch
Testoffset = Testoffset + 1
Loop
End If
End If
I have a list of links in 1 excel worksheet. They are spaced every other row if it makes a difference. I need to create a macro that clicks on every link automatically. Each link is a direct file that I need downloaded every few months. I have no Macro experience beyond hitting record to automate basic tasks.
View 13 Replies View RelatedI have an excel sheet which has a macro which captures the current time in a cell with the formula =now(). What I am looking for is that once a user runs a macro and the time is inserted into that cell then that cell should be locked and cannot be changed then. If this can be done by any formula / function or VBA.
View 2 Replies View RelatedI use Excel 2003. With a LOT of help from this board, I've written some macros for a workbook template that will be opened then saved with another name with an .xls extension. The problem is that the macros transfer data to and from another workbook template. Here's the code that's giving me problems.
Windows("BookA.xlt").Activate
Sheets("Sheet1").Select
ActiveWorkbook.Save
Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("BookB.xlt").Activate
How can I use the macro if I open BookA.xlt then change the name to BookZ.xls for my data entries? At the same time BookZ is open, I may use another instance of BookA.xlt and save that one as BookY.xls. What's the most feasible way to still use the macros?
On an excel document that was originally formatted as a number and then locked, somehow users have changed the formatting to a date. I think it is an accident, but I can't figure out how they are doing it. I have several data files on a server where several offices have access to them to input data and somehow formatting gets changed sometimes, even when locked.
View 3 Replies View RelatedI'm after some vb code that will only allow the range - PRODUCT_Selections!A2:C370 to calculate, when something is changed in the range - Input!T11:V500
I would like all other calculations in the workbook to continue as normal.
Column-A starts out empty.A user changes cell B5 and navigates to another cell (whether by clicking, tabbing, or enter-key, it doesn't matter).An "X" is placed in Column-A for that row.Preferably the user would then be taken to the cell they were navigating to (whether by mouse-click, tabbing, or the enter-key).This happens every time a change is made to a cell.I have no idea how to code for this.
I'm not sure what constraints I might have for exceptions. For instance, can it handle multiple row being pasted into or cleared at once, etc. ( Deleting columns will not be allowed in this instance.)
------------------------------------
ADDENDUM:
I continued my search and found the instructions at [url]
My ultimate goal in putting an "X" in Column-A is to identify rows with changes, so they can then be copied into an "archive" worksheet. I'm thinking this VBA version of track changes would be much better than what I was earlier thinking.
I have a workbook with two worksheets, Data and Archives. I want to let a user make changes to data in the "Data" worksheet. Then when they save or press a button all rows with edits will be copied into the "Archives" worksheet.
Appending date/time and username stamps in the trailing columns would be very handy too.
The following code works fine but now I want it to work without a userform. SO there is no userform and so no controls.
When the user changes date in a cell then a macro should run and following kind of code should execute.
[Code] .......
I'm making a worksheet with 64 dropdown boxes.
I would love a macro that activates whenever any combo box is changed, that could tell me which DropDown was changed.
DropDown1_Click() seems to be the only option i can find, but is there a way to not have to create a unique method for each object?
How do I have a macro execute anytime a checkbox, or radio button is clicked on a form? I have a macro that needs to run, but I don't want to put it in the code for every checkbox because it will complicate any changes or additions to the form.
View 9 Replies View RelatedIs there a way to set 2 cells equal, while allowing either one to be manually changed? So if I put in value of 10 for Cell1, Cell1 and and Cell2 will both be equal to 10. Then if I change Cell2 to a value of 20, both cells will be equal to 20.
View 5 Replies View RelatedHow do I protect a select range of cells from being changed? There may be some formulas in certain cells, which produce data based on other worksheet cells. These cells are not for user data input, but for data output only. I don't want end users to acidently delete the formulas in cetain range of cells, by entering a value in the cell, which then will write over the formula.
View 5 Replies View RelatedI've managed to piece together a VBA code that works perfectly for my needs. The problem is that the sheet it is on gets adjusted a lot, and I only need the code to run when certain cells are changed (rather than each time any cell is changed).
Here is the code I have now:
[Code] .....
Put simply, I need this code to run only when a cell in Range "TValueDeal" is changed. Is making this adjustment as simple as adding a couple lines of code, or is there a better way to write the entire thing?
Is there a way to cause a particular VB function I write to execute any time a value in a particular range of cells is changed?
I thought I had it when I thought I could do a sum formula, range on the cells to be checked, then call the function as a side effect of this sum. It was then that I realized I didn't know how to call VB from within an Excel cell formula.
1. Is there a way to call VB (say, sheet1.myFunction())?
2. Is there a better way to do what I want than to try to hack a side effect into a formula in a cell that depends on a sum (which "watches" for the changes for me.)
The cells would have text in them, not numbers, so using the sum-based formula would be iffy at runtime to me at best. What I really want to do is run a function on the cell value as a string. There are a lot of such cells (thousands) so ideally I would only need one external cell to activate the function (which scans all the cells I am concerned with) rather than have to paste a custom, relative formula into thousands of cells manually to have each one watch one cell, if you know what I mean.
I have a worksheet containing a cell (say A1) that has a validated list of times (data/validation/list) where the cells in the list contain times in 8 minute increments. The user can select cell A1 and manually select any of the validated times from the list. A vlookup formula on this worksheet uses the content of cell A1 to look for this time in another section of the worksheet. When the time is selected manually, the vlookup formula works properly.
As part of a macro, the macro will change the time in cell A1. I do this in the macro by selecting and copying the desired time from the validation list and pasting it into A1. Cell A1 does change to the new value, however the vlookup formula that uses A1 shows a result of #N/A. If I manually change the time, it again works. I am looking for a way to have the macro change this cell to another time and for the vlookup formula to work as it should.
I would like to create a macro that will automatically change the name of the sheet any time a value is entered into cell a1. For example, in cell A1 of sheet1 I would input "Hello", and then the tab for sheet1 would be automatically renamed to "Hello".
Here's the twist - the workbook will have multiple sheets, and I want all tabs to reflect the value of a specific cell (a1) in each sheet.
For example:
value in sheet1, cell a1 = "Yes" - corresponding tab name would change to "Yes"
value in sheet2, cell a1 = "No" - corresponding tab name would change to "No"
value in sheet3, cell a1 = "Maybe" - corresponding tab name would change to "Maybe"
etc...
The "tab change" cell would always be cell a1 in each sheet.
