This code is to find a number in Col F that is designated in E6. Currently hitting Enter will run the macro, where in the code can I change that run command to another key besides Enter or a form control button?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
If Target.Address = "$E$6" Then[code].....
I have a worksheet change macro that from the insertion of date (dd/mm/hhhh) in a cell, fills some other cells with the day number, the week number, the month,...
Everything works fine when i fill one date after the other. But when I try to fill many cells with the same day by dragging the black cross at the right corner of the cell the macro is not activated. how to activate a worksheet change macro by dragging the black cross as a mean to fill many consecutive cells in a column?
I am brand new (Day 1) to VBA programming and I am running into an issue when trying to activate a sheet based on the value chosen in my userForm that I cannot seem to solve. The error seems to be a compile error and says I am missing and End statement to one of my If Then statements, specifically:
Compile error: End If without block If
I thought if the If Then statement was all on one line that I would not need an End If statement. In fact, if I try to add an End If statement, I get an error (all proceeding text turns red).
This is the part of the code with the End If error.
[Code] .....
I'm just trying to fine tune my project to making users enter specific data into fields C17 and C19 [eg enter the word "hello" in C17 and the word "goodbye" in C19] before the command button is activated.
The existing code i am using [below] activates the command button when anything is entered.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C17, C19")) Is Nothing Then
If Not IsEmpty(Range("C17,C19")) Then
Me.Shapes("CommandButton1").ControlFormat.Enabled = True
Else
Me.Shapes("CommandButton1").ControlFormat.Enabled = False
End If
End If
End Sub
Currentley I have a worksheet(2)containing data updated from another woorksheet(1). I have a command button which runs some code for a filter ( recorded macro). can I run this code every time the worksheet(2) is activated instead of using the command button?
View 9 Replies View RelatedI have a macro that returns a lot of data and I need it to automatically resize the row in which the data is being returned. Here is an example of what I mean:
Function MyMacro()
ReturnString = "help" & chr(10) & "me"
MyMacro = ReturnString
' resize the row
' need to define "Location", based on the location of the cell in which the data is being returned
Rows(Location).EntireRow.AutoFit
End Function
Even if I specifically hardcode the location of the row to be resized in the macro, it still doesn't work. The cell has "Wrap Text" turned on.
I am currently putting together a Directory and want each of the 19 programs and the worksheets to be maximized when they are opened.
View 1 Replies View RelatedI have been building a program in Excel and one of my lines of code just stopped working. I am just selecting a different sheet, say I am Sheet1 and I want to jump to Sheet2:
View 3 Replies View RelatedI'm trying to auto sort a list of ranks that are linked to another sheet. My sort code works when it referers to input number values, but when it refers to cells that have formulas, it does not recognize the value from the formula, only the formula itself. Any ideas would be welcome.
View 4 Replies View RelatedI want to activate a specific cell on a specific day...namely today.
I have a excel spreadsheet with various dates that when it reaches today i want another cell to activate and be shifted to the active cell.
I have the following columns:
Date of transaction Earned Points this transactionActive PointsActive date of pending points Pending Points
07 November 2012 R 40.00 08 December 2012 R 40.00
Now, i want the pending points to activate and be shifted to active points on 08 december 2012. my active date of pending points columns is automatically calculated from 07 november 2012 by formula =C3+31
Is there a way to either change this so that it lets me to select the whole area or a way to make a macro to do what this does to one cell?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("M13:IR458")) Is Nothing Then
Select Case Target.Value
Case "1"
Target.Font.ColorIndex = 20
Target.Interior.ColorIndex = 10
Case "Good"
Target.Font.ColorIndex = 2
Target.Interior.ColorIndex = 35
Case "Stable"
Target.Font.ColorIndex = 2
Target.Interior.ColorIndex = 27......................
I am trying to set up vba as part of a macro that will do the following:
Open a master workbook
Open a data workbook( There will be a few of these to process)
Copy the data from a data workbook sheet (INPUT SHEET) to INPUT SHEET in the master workbook.
I will rename the master and then continue with the same process for the next data workbook.
I cannot copy/move the input sheet because of an MS bug whereby all of the sheet coding is lost whe you insert a sheet into a workbook.
I have set up a sheet in the master workbook that lists all of the data workbook names and paths I set the data workbooks up with a string variable name of TRGT.
I can get the coding to open the TRGT workbook but am having trouble getting the subsequent sheet commands to work in order to extract the data.
Here is a snippet of the
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.
My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
I want activation of userform to hide worksheet, but as soon as the Userform is closed, the worksheet should show.
I have attached file to aid.
I want to sum/add values only if change occured based on Ctrl+down basis. For example, this is the column which starts from A2.
A2 5
A3 5
A4
A5 9
A6 9
A7 3
A8
A9 6
A10 3
A11
Now, what I want is to autosum values one time only whenever there is blank cell for around 1000 rows. I have been able to figure out the VBA code for this. Final result should look like this.
A2 5
A3 5
A4 5
A5 9
A6 9
A7 3
A8 12
A9 6
A10 3
A11 9
AS per the attchement, I add a date in the cell H2 and when I select in the cell I2 the date in the column K changes as per the =IF formula..
My question is the following: Would it be possible, once I select the option in I2 to have the formulas in the column K changed for value? I put a example recording a macro!
HTML Code:Â
Range("K2:K4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I really wish to have that automaticaly done once I select the option in I2 without running manually a button.
I have a workbook save as "file1.xls" All the macros and stuff work great. I want to use this a template. The idea is to open this book at the beginning of each year and save it with the year in the name. This way I have a file for my business stuff for each year.
I have been saving along the way so I have it named "file1.xls, file2.xls, file3.xls.......up to file 7 right now."
When I save the name, the macro stop working. It seems like they are attached to the original name of the file. I will eventually save this file with a new name for my company and transfer it on to a different computer.
How do I fix this so that they will work with whatever the book is saved as?
Is there a way to have the macros save with my current workbook and transfer to my other computer when I get this project done?
I keep recording this macro, but the problem I run into is that the active sheet is always the specific name of the sheet. I need a general name so that the macro will work on any given sheet. On the sheet I am viewing, I simply want to change the tab color to black using a macro.
View 2 Replies View RelatedI would like to change an existing Macro……i.e. the current date……which is ,,,,,CNTRL +; …… I want to make it CNTRL + e………..I tried to make my own by running a new macro……but obviously I am doing something bass ackwards…..I tried to look up the current one……that is CNTRL + ; and see how they did it……but couldn’t find that either
View 2 Replies View RelatedI have code which changes the worksheet tab names based on contents of a cell. I borrowed some very useful code from a previous thread. I'd like to modify the code so that the tab name updates everytime the cell contents change.
My code is below:
Code:
Sub ReNamer()
For L = 3 To 9
Sheets(L).Name = Sheets(L).Range("A1").Value
Next
End Sub
I have a sheet called Summary. On that sheet, Cell O6 has a drop down with two options, when you change these options, a number of other cells on the same sheet automatically change (just using formulas). Including a cell that I've given the named Range of 'testCell'. Based on the drop down, test cell will either = 8 or 9.
What I also want to change is the format of a range of cells whenever O6 is changed - but only when O6 is changed.
However, the following code does not work. It works fine if i remove the 'If Target.Address = "O6" Then ...' but doesn't work with it included.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "O6" Then
If Range("testCell").Value = 8 Then
Range("P10:AD27").Select
Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
Range("O6").Select
[code].....
excel/vba/macro as well
I want to make a macro, which can look up a specific cell value in a column and then replace this value only the first time.
E.G.:
value - 25
desired_value - 31
Peers 30
apples 25
oranges 25
I want it to check the values in the range and change the first 25 to 31.
Basically, there are 5-6 worksheets and I want this to go into each sheet and update the pivot table by changing the dates to today from pulldown menu in pivot table.
But how do I replace that in below recorded macro?
Workbooks.Open Filename:= _
"M:xxxxxxxxxDaily TemplateAGT_TM3 evolvement.xlsm" _
, UpdateLinks:=0
Range("AU11").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Prices ObservationDate].[Observation Date].[Observation Date]"). _
VisibleItemsList = Array("")
[code].....
I have a problem in changing a date automatically in a macro.What I want t to do is the following: On sheet "Stock Count" at cell I1 is a date. I want to open new sheet and copy "Stock Count" to this new sheet then rename the new sheet to the date on "Stock Count" cell 1. I have the following:
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Stock Count").elect
Range("I1").Select
Selection.Copy
Sheets("Sheet1").Select
Sheets("Sheet1").Name = 14-Apr-14"
Sheets("Stock Count".Select
Range("A1").Select
ActiveCell.Paste
I need a macro that will change a number's sign. To go from neg. to pos. or pos to neg. I need this macro to execute this on all selected cells. So, for example, if I select A1:G35 and execute this macro via button or short cut, all those selected cells with numbers will flip signs.
View 9 Replies View RelatedIs it possible to run a macro when the value of a partcular cell is changed? (and if so how!)
View 9 Replies View RelatedIn column A if have strings like this: X-100-X10 or C-100-1X00 etc.
I need to change the X at the end of the string to "." e.g. X-100-.10 or C-100-1.00
Can't figure it out since there can be 1 "X" or more than 1 "X" and the second "X" that needs to get changed can be in a different location i.e. not alway 3 from the right or 7 from the left.
Is there some macro code I can use to make this change? Otherwise I have to change hundreds of these manually.
I received a great little routine from you guys to a question which was a follows
Can Excel do this?
I have a huge spread sheet - The formulas in each cell reads as follows:
='[1.xls]Community Libraries'!$A$9. I would like to copy the cell all the way down the column, but only 1.xls must change to 2.xls and 3.xls etc. Can Excel copy this way?. I'm using Excel 07 on this pc
The response was:
Sub PutFormula()
For i = 1 To 80
Range("A" & i).Formula = "='[" & i & ".xls]Community Libraries'!$A$9"
Next
End Sub
Can this be modifed to:
A) Start on row 6 and end on row 85 of each Column A to CZ
B) Modify the end bit of the formula as follows Community Libraries'!$A$9&"/10"
I have a Excel database query which of which i import into Sheet 1. On a daily basis I need to edit this query and change a critea field to yersterdays date. Is there a way in which I could run a macro to change this query for yesterdays date without having to manually go into the query?
I have tried to run the macro, however I can only run this if I have a specific date in my code e.g. 11/03/2007 and not a formula to show yesterdays date.