I'm trying to come up with some code that will allow a user to mouse click a cell to cut it, then mouse click another cell to paste it, then it toggles back to mouse click a cell to cut it. It would be a 2 step process for the user - select cut then select paste.
I am aware that I can use single changing events in worksheet change events. For instance, if column 1, or A is changed, do something. This is only a single If statement, i.e. either the condition is true, or not. What I am not sure is if I can use two changing events, i.e. two conditions. For e.g. I would like if Column A value is X and Column B is "Active", action it, but only if two conditions are true.
For.e,g. The below syntax does not work. If it is only column A, it does work, but I want both A and B to be true, then copy and paste the target does not anything.
VB:
If Target.Column = 1 Then
If Target.Column = 2 Then
If Not Intersect(Target, Range("A2:A" & Rows.Count)) Is Nothing Then
If Not Intersect(Target, Range("B2:B" & Rows.Count)) Is Nothing Then
If Target.Value = "X" And Target.Value = "Active" Then
In both instances I want to put a date stamp of the column immediately after the defined columns below.
The first issue is that when it runs the 1st change event, it only puts the date stamp in the top row of the column. The change in the "Inportedshapeall" column would almost always be brought about from range being copied from another column, however, I would still like a date stamp in every cell of the column.
The second issue comes about when I try to run the second change event. It simply seems to get stuck. I have tried Else, Else if, removing End ifs. I am not sure how best to define the events in order to ensure it loops through both.
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("IMPORTEDSHAPEALL")) Is Nothing Then
With Target(1, 2)
[Code] ......
2 Woksheet Change Events? I have a question similar to one that was posted about 2 worksheet change events which I found out is not possible and so I have to merge the codes together and I don't know how. I'm facing a problem in the advanced filter when I remove the last item to be filtered and takes forever to calculate.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$F$2" Or _
Target.Address = "$F$3" Or _
Target.Address = "$F$4" Or _
Target.Address = "$F$5" Or _
Target.Address = "$F$6" Or _
Target.Address = "$F$7" Or _
Target.Address = "$F$8" Or _
Target.Address = "$F$9" Then
Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("Criteria"), Unique:=False
Exit Sub
End If
If Target.Column = 3 Then
If Target.Value = "YES" Then........................
I am trying to have multiple change events work in one worksheet and can not get it to work at all.
The simple setup is, I have a column for each month of the year.
I have several rows of numbers beneath each monthly column.
What I want to do is if I enter a date in say January (my change field), I want it to "ClearContents" out all of the data in the August column.
This works fine currently.
But say the next month I enter a date in February, I want to "ClearContents" in the September column. And so on for each month.
My various attempts at this does not recognize the second change agent, so nothing occurs.
Should I try to "call" each section, or use "Else If", or some other idea?
I've tried attaching the worksheet in case that helps to see what I am attempting.
There are extra "items" on the side and below the table that will be removed if I get this "change event" working. So this worksheet is a rough draft so far.
I realize that it’s not possible to have on one sheet, multiple Worksheet Change Events, so I tried to come up with an alternative method of handling it, but quickly realized that it’s beyond my capabilities at this time.
I would like to set up a worksheet change event on each of a number of identical sheets. Each Change Event will run exactly the same macro. The exception is the “DeptStr” string value that is relevant to the sheet calling the macro .
Each sheet will be a different department.•If the Target in either range has data added, then data will be added to the cell offset one column to it’s right.
•If the Target in either range has data cleared, then data will be cleared in the cell offset one column to it’s right.
•There can only be one sheet active at a time.
•Only one sheet has data entered at a time.
•The two non-contiguous ranges are of equal length. Ex. (C3:C52) and (E3:E52) and are uniquely named in this case,
but they are not a necessary requirement in order to solve this problem if there is a more practical method.
If a cell in some parts of the worksheet (SSMa,SSDi,SSWo,SSDo,SSVr,SSZa,SSZo in the code) are selected the input (4 digits) should be converted into time (00:00).
Cells in some other parts (Util,Assis) require 6 digits and should be converted into time (00:00:00).
I had the code working in seperate worksheets but combined (as is shown in code bellow) only part of it works.
Only the input entered into the 4 digit part (SSMa,SSDi,SSWo,SSDo,SSVr,SSZa,SSZo) is converted correctly.
The input in the other parts (Util,Assis) is not converted (so when 800 is entered I do not get 00:08:00 but I get the hour equivalent of 8 days (19200:00:00) and logically the message from the EndMacro "You did not enter a valid time".
The cell formats are correct [u]:mm and [u]:mm:ss.
To me it looks like it does not jump to the 6 digits part when need but I can't figure why.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("SSMa,SSDi,SSWo,SSDo,SSVr,SSZa,SSZo,Util,Assis")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then..........................
I have a worksheet that I would like to color a range of cell within a row whenever the value of a certain cell in that row changes. For example, if cell A3 have a value of East that row color will be Blue with white fonts, if value is West the row color will be Green with Black fonts, if value is North the row color will be Red with Yellow fonts. This should apply to any row whenever the value is Column A is changed.
The codes below allows me to change the color of the cells in Col F & G dependence on the value of Col G. I like to add another condition and that is if Col K the value is cancelled or closed the whole row will be CellColor = 48: FontColor = 2
Sub Risk_Color()
Dim c As Range, myFontCol As Integer, myCol As Integer
For Each c In ActiveSheet.Range("f7:g20000")
myFontCol = xlAutomatic
myCol = xlNone
Select Case c.Value
Case Is = 1, 2, 3
myCol = 34....................................
I have a previously working _chnage event that I am now trying to nest a similar event in. Neither event works now. What am I doing wrong?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim refrange
If Target.Address(0, 0) "C55,G107" Then Exit Sub
refrange = [MATCH(C55,lst_AgentType,0)]
With Sheets("NewInput").Range("d63:r63")
If refrange = 1 Then
.NumberFormat = "#,##0"
ElseIf refrange = 2 Then
.NumberFormat = "#,##0.00"
Else
.NumberFormat = "0.00%"
End If
End With
If Target.Address(0, 0) = "G107" Then
refrange = [MATCH(G107,lstCommRev,0)]
With Sheets("NewInput").Range("E107")
If refrange = 1 Then
.NumberFormat = "#,##0.00"
Else
.NumberFormat = "0.00%"
End If
End With
End If
End Sub
I have set up a selection change routine which works if the cell has previously been used or if you make an entry and then change it again. I have tried to delete the empty cells 1st and then save the document, when opening the document it I enter a value in a previosly unused cell the change function does not appear to be called. There is an auto open routine that runs as well, there is more code in use but it fails at the start point.
VB:
Sub Auto_Open()
'
' Auto_Open Macro
'
'
Application.EnableEvents = True
[Code] ......
I have had a TREMENDOUS amount of assistance from a true gentleman, Jaslake; aka John. In an attempt to quite monopolizing his time I wanted to throw this at the board. I have a number of sub sheets with exact ranges of (B4,d4:b44, D44) update an individual cell (H6:H86) in the” summary” page. My attachment explains it in detail but currently I will show you the formula that resides in (H6) of "Summary". (H6) = Sheet "1". This formula only covers (B4,D4:B10,D10) for example purposes but I need it to run all the way through (B4,d4:b44, D44) for all 80 sheets.
=('1'!B4) & "-" & ('1'!D4) & " | " & ('1'!B5) & "-" & ('1'!D5) & "|" & ('1'!B6) & " - " & ('1'!D6) & "|" & ('1'!B7) & "-" & ('1'!D7) & " | " & ('1'!B8) & "-" & ('1'!D8) & "|" & ('1'!B9) & " - " & ('1'!D9) & "|" & ('1'!B10) & " - " & ('1'!D10).
Results look like this:
March 01, 2009-Today looks good | March 02, 2009-Today looks bad|March 03, 2009 - Today we had issues in Boston|March 04, 2009-Issues in Dallas | March 05, 2009-Issues In New York|March 06, 2009 - New York is complete|March 07, 2009 - Dallas is Complete
NOW, with this said what I really would like to happen, if it is possible, is to have the "Summary" (H6) only have the last range that has been updated showing. So if currently B4,D4 is showing in (H6) once the user types into B5,D5 then it replaces B4:D4. I would like this action to repeat itself until the user has completed their event. The reason I can not stick with a formula in (H6:H86) is because I have a macro running a copy.paste, then clear.contents once a selection is made in Column 4. The information must remain on the sub-sheets but it can be replaced in “Summary “. “Summary” is only a quick view of the current status while the sub sheets are a log of all the work done. So, the following
I have a workbook with several worksheets. One sheet ('Forecast') contains 12 ComboBoxes, which, on a Change event, run a series of macros to show the users certain information and provide certain options: protected 'Budget' or 'Actual' data from a hidden datasheet, or unprotected 'Forecast' cells that respond to user changes. In other words, I can't simply use a series of 'If' statements or VLookups with conditional formatting to get around the ComboBoxes.
OK - So in ordinary use, the ComboBoxes and macros are working as intended.
But there is one situation that causes problems:
When a SaveAs command is used to save the model under a different name, all 12 ComboBox Change Events are somehow being triggered.
So what is it about a SaveAs command that triggers ComboBox Change events?!
And whatever it is, can I turn it off?
(This happens in Excel versions 2000, 2003 and 2007.)
I receive real time data ( Last Trade Price ) of soybean commodity futures through DDE in to excel ( cell A1 ). During market hours A1 will keep updating every milliseconds or seconds.
My cell B1 ( =A1 ) will have same value as A1 and will update at the same time.
What I'm looking for - A macro code so Whenever value in B1 is between 9.5000 and 9.5050, I want run a subroutine ( similar to calling a macro ). Since B1 would change dynamically so everytime B1 comes between range 9.5000 and 9.5050 a subroutine is fired.
I'm planning to CALL following subroutine -
Sub BuyBeanst()
Range("N9").Activate
ActiveCell.Value = "Y"
Range("T10").Activate
ActiveCell.Value = "Y"
Range("T11").Activate
ActiveCell.Value = "Y"
End Sub
Is there a keyboard short cut that toggles between multiple tabs in a file similar to the 'Alt+Tab' shortcut that toggles between open applications?
View 3 Replies View RelatedI have a UserForm with a Text Box, I populate that Text Box with a number (say 5) and then the following code runs:
Code:
Private Sub tbOverrideMokWh_Change()
Application.EnableEvents = False: Application.ScreenUpdating = False
With tbOverrideMokWh
[Code]....
After the Sub is run 1 time, it runs again. Why? I've disabled Events?
I'm using the following code in a worksheet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
col = ActiveCell.Column
Range("output") = ActiveCell.Offset(0, -(col - 4)).Value
End Sub
In case it's not obvious, the macro places the value in the active row and 4th column of the worksheet into the range "output".
The problem is, the worksheet is large and somewhat slow to recalculate. This macro forces a recalc on any selection change, but i only need it to run when the row selection changes, not the column.
I'm sure there's a straightforward way to reprogram this.
Attached is book in which, when a choice is selected from Drop Down list in ColumnF the macro has to do the need.
When the macro was written it was working well. But when I tried to change it as a Worksheet_SelectionChange event nothing is happenning even though a choice is selected from drop down list.
In cell A1, I have the month number (eg, 1, 2, 3,). The month number reflects current month and will automatically change with every month. For example, right now it’s 6, next month it will automatically change to 7. Each two columns in Range A10:X20 represents the data from January to December. I want to use a worksheet event to change the background of the current month two columns in the range to yellow color and the two columns in the range will be visible when I activate this sheet.
View 3 Replies View RelatedMy user wants to change it so that, when the word "Triang" is selected from a dropdown box on each line the formatting goes back to none......or ClearFormats.
Can this be done with a Worksheet_selectionChange or similar.
I can't even get my head around this at the moment.
What the user is saying is, if the Formats haven't been cleared on certain lines, the user of the sheet hasn't addressed the needs for that particular line.
For r = lrow To 15 Step -1
If Range("M" & r).Value "" = True Then
Range("N" & r).Value = "Triang"
Range("N" & r).Interior.Color = vbRed
Range("V" & r).Value = "Triang"
Range("V" & r).Interior.Color = vbRed
End If
Next r
I'm trying to write some code where if the user hits the Enter key on a particular cell an input box pops up. At the moment I've got some code that activates the input box whenever the user selects or scrolls over the cell. The problem is that the cell is a protected cell (as it has a formula in it) so double-clicking isn't an option.....
View 8 Replies View RelatedThe following macro will put a checkmark in the range of A2:B100. I would like to use the current macro along with two or more other Worksheet_SelectionChange. One of the SelectionChange to put "Williamson" in the range of L2:L100 and another SelectionChange to put "Michaelson" in the range of M2:M100? Is it possible to use more than one Worksheet_SelectionChange
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A2:B100" '
I am getting US formatted dates with this procedure, I require UK dates, but I don't understand why its giving me US format
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If UCase(Sh.Name) "ALL BILLS" Then Exit Sub
With Target
If .Row > 5 And .Column = 19 Then
If Cells(.Row, "B") = "" Then
Else
Application.EnableEvents = False
Cells(.Row, "S") = "CLOSED"
Cells(.Row, "E") = Format(Date, "DD/MM/YYYY")
Application.EnableEvents = True
Range("E:E").EntireColumn.AutoFit
Range("S:S").EntireColumn.AutoFit
End If
End If
End With
End Sub
In this file, I have a Work Summary worksheet in which I'm extracting some data from the other worksheets to "summarize" all jobs into one nice work summary log On Basis of Average of data from 3 other different sheets A,B,C
The following form works great to if onyl formula calling is applied
=AVERAGE(INDIRECT("'"&$C6&"'!c3:c7"))
C6 = Worksheet name or Worksheet tab name ....
how to Keep Selection in Column But Change to 2 rows less. I have the following code that works.
View 2 Replies View RelatedI am having some difficulty working out how to activate the SelectionChange command if the user changes a cell in a particular column. Based on the fact that the column number may change the constant in this column will be that on row 7 the name will be "Fund Size".
Therefore my question is how do I get VBA to run my code if a user changes a cell in the "Fund Size" column...and underneath the "Fund Size" header (i.e. row 8 or greater).
How do I change the selection of a range with a variable, and not a hardcoded number in XL2003? I have to update a set of spreadsheets every month, and it's a hassle to have to constantly open my pivot table worksheet, copy, open the summary worksheet, paste ... etc. etc. etc. The code below is my attempt at creating a ComboBox with "January, February, March, etc." and every time I select a particular month it will automatically copy data from my pivot table worksheet into my new summary worksheet in the correct column. The range of data from my pivot table worksheet will never change, so I have no problem hardcoding that in, but based on which month is selected will alter which column the data goes in in the new worksheet.
I want to be able to write code for one month (say January) and then when I want to use a different month (say February) I can just change the column number and call up the originial January code.
Private Sub ComboBox1_Change()
Dim ColNum ' This is the variable I want to change based on which month is called
If ComboBox1.Value = "January" Then
ColNum = 1 ' Column number for Column A- Where my January column is
Elseif ComboBox1.Value = "February" Then
ColNum = 2 ' Column number for Column B- Where my February column is
' etc. etc. for each month
End If
Call January
End Sub
Private Sub January()
' Just a quick msgbox to make sure the previous macro is calling this one
MsgBox "Is this macro running?", vbQuestion + vbYesNo, "Check"...........................
I just loaded 2007, and among the things driving me nuts is I can't figure out how to change the color of highlighted cells that I have selected. The default color is so close to the normal background color(White) that it is difficult for me to distinguish between selected cells and unselected cells.
I've tried the built in help but couldn't seem to search for the right phrase to bring up a relevant answer. I've had the same problem when trying google and Microsoft's office 2007 help site. I also searched the forum before posting and came up blank.
How do I change the color of a row, based on the users selection from a drop down box in that row? I have four driving types in my drop down , Towing - Town Driving - All highway - Mixed. I would like each driving type, if selected, to change the color of the entire Row of Data to a different color. I have been able to use conditional formating to change the drop down cell itself, but that is it.
View 1 Replies View RelatedI have attached a work book.
At the moment when you click the macro botton on the sheets a calender shows and you can select the inspection done tab, this gives you a list to select witch then updates the selected task in the sheet.
How can i get this to allow single or multiple sections so that if more than one task is completed on the same date they could be updated all at once.
final1.xlsm‎