Worksheet_SelectionChange
Jul 4, 2006
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
View 9 Replies
Apr 27, 2007
Is it possible to have two Worksheet_SelectionChange events for the same worksheet if so how. If not what options do i have?
View 3 Replies
View Related
Jan 6, 2008
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.
View 9 Replies
View Related
Sep 19, 2008
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.
My macro security setting are low.
View 9 Replies
View Related
Nov 21, 2003
Worksheet_SelectionChange(ByVal Target As Range)
from executiing each time i like to execute
Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
View 9 Replies
View Related
Apr 27, 2007
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
View 7 Replies
View Related
Jun 12, 2006
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
View 2 Replies
View Related
Jul 13, 2007
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?
View 2 Replies
View Related
Aug 22, 2008
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....
View 4 Replies
View Related
May 19, 2009
Excel 2003, Windows XP SP3
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
Sample ...
View 9 Replies
View Related
