I have popped the code below in the worksheet and it will not work on my machine (Vista + Office 2003). However, it works fine on my colleagues machine (XP + 2003).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 4 Then GoTo usevalue
On Error Resume Next 'MUST reenable events...
Application.EnableEvents = False
ActiveCell.Offset(1, 1 - Target.Column).Select
Application.EnableEvents = True
usevalue:
ActiveCell.Copy 'Put value into clipboard (marching ants)
ActiveCell.Interior.ColorIndex = 36
End Sub
I have tried different code from different web sources on different fresh files. This includes the Worksheet_Activate() function.
I cannot solve problem 1, thus I tried workarounds, however there are also problems with them (problem 2, problem 3). I do not use .activate or .select in my code. I have recreated the problem in sample workbook.
Problem 1:
I get a blink when I call another sub from Worksheet_SelectionChange event. Event is located in Sheet module, CellChange procedure - in seperate module
I have written the following code which will use "U10" to divide by 12, & put the results on "H10.S10" if "U10 is not empty.
The results work fine except that if I delete the value in "U10", the formula "= sum($U$10/12)" will remain in H10.S10, if there any way that this formula do not remain in H10.S10?
Because this code should work from row 10 to row 47, like to ask how to write in short vba code instead of repeating the code with the need to change the row number?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("U10").value <> "" Then _ Range("H10.S10").value = "=sum($U$10/12)" End Sub
I found this code on one of my many searches, that works great.
(can't remember where I got it or who wrote it, (My deepest apoligies to the author))
Public pRule
Sub butRulerToggle_Click() pRule = Not pRule Selection.Select End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If pRule Then For Each aCell In ActiveSheet.UsedRange If aCell.Interior.ColorIndex = 27 Then aCell.Interior.ColorIndex = xlNone Next On Error Resume Next For Each aCell In Application.Intersect(ActiveCell.EntireRow.Cells, ActiveSheet.UsedRange) If aCell.Interior.ColorIndex = xlNone Then aCell.Interior.ColorIndex = 27 Next End If End Sub
My question: This works in a sheet module. How can I put it in the This Workbook module so it works on all sheets.
By the way, what this does is highlight the whole row on a clik of a cell, leaving any color formating that was initially there alone.
Very useful if you are looking at say A10 and then want to look at Z10 without losing focus on the row.
I have this code that will update column "AW8:AW60" when a date is entered into"z8:z60" if column"c8:c60" equals 4. It works great if the c column doesnt change if it does it doesnt recaluate. What I need it to do is if the number in "c8:c60" changes it recaulates.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim msgg As String Dim msgg2 As String Dim NewDate1 As Date Dim c1 As Range If Not Intersect(Target, Range("z8:z60")) Is Nothing Then msgg = InputBox("Please Enter A Date..." & vbLf & vbLf & "(MM/DD/YY)") If msgg = vbNullString Or Not IsDate(msgg) Then MsgBox "You Did Not Enter Date!", vbCritical With Target
When the next empty cell in column A is selected, add a formula in the same row, column H. I'm not sure on how to hard code this and used the offset instead. So maybe instead of offsetting, i should/could change the references? However, as it is, it also adds the formla in column H when column G is selected and data is entered. I cant recreate this with any other column though.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A5:A65000")) Is Nothing Then If ActiveCell.Offset(-1, 0) = " r " Then If ActiveCell.Offset(0, 7) = "" Then ActiveCell.Offset(0, 7).Formula = "=if(RC[-1]="""","""",RC[-1]+120)" Exit Sub End If End If If ActiveCell.Offset(-1, 0) = "" Then MsgBox "Please select the next empty cell/row!" Exit Sub End If If ActiveCell.Offset(0, 7) = "" Then ActiveCell.Offset(0, 7).Formula = "=if(RC[-1]="""","""",RC[-1]+120)" End If End If End Sub
The attached file receives information from another program in cells A2:D2. It then carries out a copy/pastespecial, and then does a copy insert. The script is supposed to update when new data enters the cell, however it keep adding an additional null line. Does anyone know why this is or how to fix it?
I have some code which enables new comments to be added when a user double clicks a cell, but I want to restrict this to a specific range B5:B125. How do I change the code to reflect this, and add the current Date to new comments added. Here is the first section of the code which sets the range etc...
Option Explicit Public oldRange As Range Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) On Error Resume Next Dim rng As Range Set rng = Target(1, 1) oldRange.Comment.Visible = False With rng If Not .Comment Is Nothing Then If .Comment.Visible = False Then .Comment.Visible = True Else .Comment.Visible = False End If End If End With Set oldRange = Target(1, 1) End Sub....
I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range) If target.Column = 1 Then ThisRow = target.Row startRow = 1 i = 1 Set ws = ActiveSheet maxRow = Cells.SpecialCells(xlLastCell).Row maxCol = Cells.SpecialCells(xlLastCell).Column ActiveSheet.UsedRange.Interior.ColorIndex = xlNone Do While i
I created a spreadsheet with many CF's and save the sheet on our network, however, when my manager tries opening it the CF's do not work. they are there when I click on CF > Manage Rules, they just not working, neither is the Data Validation Drop Downs?
In A column the date something is received will be entered.
In B column there is a drop down list that has "insured" "Fee" "Aged Debt" "Other".
In C column I am trying to get another date to populate dependant on what is picked from column B, so if Insured is picked then 1 working day needs to be added to the date in column A and populate in column C. If any of the other 3 options are selected 5 working days needs to be added to the date in column A and populated in column C.
I have been using this formula: =IF(B2="","",IF(B2="Insured",A2+1,A2+5)) however this is not working days and I am stuck how to get to add working days
I have a dropdown answer column (J) to select an answer and in column (K) next to it that scores the answer. I'm trying to sum the score of (column K) in cell K7 but can't seem to get it to work.
I think this may be impossible but what I am hoping to do is in B3 Return the date that is 3 working days before the date in B2. Working days being Monday to Friday in this case. so if the date in B2 is the 16/04/2014 3 days before this is the 13/04/2014 which is a Saturday so i would want the formula to return the 12/04/2014 which is the Friday. Is this possible?
The last part of the formula is the problem, the first two arrays are conditions and the last array is a sum if f4 a period of the year is equal or less to the data sheet tab it is summing.The formula below works fine, but when the last condition is added it doesn't work.
I am using and index match formula that looks at names and dates. The dates are fine. The names are fomatted the same in both lists. Two names identical in each respect give me a #N/A result. It should match but it does not. How do make each formatted the same. Very strange......
The following is a simplified example of my problem: I have a list of product item numbers in column A. Next to each item number in column B, is a hyperlink to a PDF file relevant to each item. Elsewhere in the sheet, is a drop down box containing the item numbers. Next to the drop down box is a HYPERLINK(VLOOKUP) that fetches the relevant hyperlink depending upon the selected item in the drop down box. Problem is, when I select the ‘lookup’ link, I get an error message ‘cannot open the specified file’ (the original Hyperlink works fine). Does anyone have a solution to this problem (preferably without resorting to Visual Basic?
Sometimes when I right click on a worksheet (filled with formulas, formatting, etc)and select move or copy and then click on "create a copy" a new BLANK tab is created rather than a new worksheet with my data. I'm not sure why this happens sometimes. This usually happens from a spreadsheet I downloaded from a website that I work on.
My workaround is just to copy and past the whole sheet to the newly created tab, but it's frustrating that it doesn't work the normal way.
Got this neat Calendar from another thread on this site,but it has problems. Played around with what little skills I have with no luck. When you open the calendar go to Jan, 1992 click on first four days and everything works. When you click on Thu 2 the problem begins.
I notice that sometimes the "Go To" function does not work. When I click on the named range Excel just brings the cursor to a the first cell of a worksheet rather than the actual cell/range. I find that I need to restart Windows in order to fix this. A simple restart of Excel does not work.
I can not figure out why only the color index 5 and 8 are working in the example- (1). To tun it say "no" to the initial prompt that comes up and select 1 from the dropdown in A2. Before some additional functionality was added it was called TestBook and that highlighted appropriately.
Is there a way in excel to say if i select Item1 and it is 100mm x 30mm x 100mm, excel will say this will fit into a compartment called A1 and may also fit into a compartment called B6.
I have a list of all the compartments and what the internal dimensions are. So is it just a matter of saying if these dimensions are within those of the compartment then that item will fit in.
I have a list of some what 6000 Items that have dimensions. I may pick 200 or so of these items to fit into special compartments of a machine. Because of the varying sizes i need to know what compartment each item will fit in so i can build a machine big enough and get the right size compartments.
