Worksheet_SelectionChange Event

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


ADVERTISEMENT

Limit Worksheet_SelectionChange Event To Specific Column

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

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 View Related

Two Worksheet_selectionchange

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

Sub Worksheet_SelectionChange Not Working

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

Worksheet_SelectionChange Vs. Worksheet_BeforeRightClick

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

Worksheet_selectionchange On 2 Columns

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

Worksheet_SelectionChange - Execute Error

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

Worksheet_selectionchange Adding Extra Line

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

Excel2003: Screen Updating Not Working For Worksheet_SelectionChange When Another Sub Called

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

Worksheet Change Event :: (ByVal Target As Range) Event In Module After Creating A Sheet

Mar 27, 2009

Is there a way to write a Worksheet_SelectionChange (ByVal Target As Range) event in module after creating a sheet in VBA? I constantly delete a sheet, then repopulate it with a new one that is empty, but I need to add some code that happens if they should change a particular cell. It worked when I ran it on a worksheet without refreshing, but as soon as I cleared and repopulated the sheet, it was gone. Is there a way to preserve this?

View 9 Replies View Related

Change Event Causing Event To Fire Again

Aug 7, 2006

I have the following code, which works perfectly:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TotalDays As Integer

TotalDays = Range("C65536").End(xlUp).Row + 1

The code points to the next blank cell so the user can input a value. Each time the user enters a value I want to re-run the code so that the colour of the cell changes.

However I also want to perform various calculations on the sheet. However this means the sheet is being changed and so continually repeats my code.

How do I add the following, to my previous code?

Range("E8").Value = Cells(7, 6) * 2.5

View 9 Replies View Related

Event Sub From Run Function Event

Aug 5, 2014

Looking for the syntax that will allow me to code an event sub routine, based off the event of a specific function e.g. findnum being run.

View 14 Replies View Related

Event Name

Dec 14, 2009

What would be the name of the event where if I select a particular cell in Sheet1 it triggers something in say Sheet2?

View 9 Replies View Related

Conditions On VBA Event

Oct 24, 2009

Working in Excel 2003. I have a VBA code that, if a particular option is chosen from a drop down box, then a message box appears. What I'd like to do is alter this code so that if cell J5 has "Text 1", "Text 2", or "Text 3" then the message box does not appear. Here's my

View 3 Replies View Related

Event Procedure

Dec 11, 2007

I've created a macro with a custom dialog box, but I don't know how to make the transition from when I make the dialog box pop up, the user enters the information, then they click "Continue" or "Cancel" or whatever it may be, how to do I make it happen from there out?

Do I make the command buttons a boolean and if they click it's true? How do I make it work?

View 14 Replies View Related

Tab Key Change Event

Apr 19, 2009

I have a user input box (VBA) with two columns of data entry. The leftmost column has text boxes labeled color1, color2, color3, etc.. The rightmost column has text boxes labeled tag1, tag2, tag3, etc..

I have the TAB sequence set to go from color1 to tag1, color2 to tag2, color3to tag3, etc..

A user can inadvertently tab over the color1 (or color2, etc. columns) into the tag1 (or tag 2, etc. columns) column without entering data in the color column.

Is there coding to allow a TAB key entry to be a Change Event such that, if a user TABS out of color1 without entering data, a MsgBox could signal that they must enter data in the color1 field before they can continue?

Alternately, can you suggest a different approach? The goal is to require an entry in the leftmost column (color1) before they can proceed to the tag1 field. Of course, they are given a "Cancel" option.

View 7 Replies View Related

Hyperlink Event

Dec 8, 2009

I have a hyperlink within Sheet1 (Functionalities) of my workbook that looks like this: ...

View 13 Replies View Related

Event Not Being Recognized?

Dec 12, 2011

I have a worksheet used for scheduling. When a members time is updated, it updates the counting cell for that time by subtracting 1 (thats simplified, the forumla is more complex than that).

I have five teams and five workbooks for each team to do it's own scheduling. In just ONE workbook, the Worksheet_Change() event has stopped executing. It's fine in the others. I renamed the workbook to archive it then put another workbook in it's place and now that one works just fine.

The workbook that I've archived, I hate doing that not knowing what would cause the Worksheet_Change() to stop being recognized. There is no code on the sheet or related to the sheet that would stop it or cause events to be cancelled.

I wanted to know if there is some secret keystroke combination that may have been inadvertently clicked that would cause events to firing or stop being recognized?

View 4 Replies View Related

Event By Hour And Age?

Apr 28, 2014

I have a table on excel that I would like to have an event calculated by the hour and would like to know how to, ex:

1900
2000
2100
2300
00-0100

0
1
0
2
0

that above is where I want the formula to calculate the following:

Activiy 1
2015

Activity 2
2310

Activity 3
2348

Also I would like to do something similar like that but for age, ex:

18-24
3

25-40
1

41-59
0

[code].....

View 8 Replies View Related

Checkbox Event

Dec 10, 2006

I need to have a check box, that when it is checked the user must fill in a cell.

View 9 Replies View Related

Worksheet_Change Event

Dec 31, 2006

I have two workbooks that have the following
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myColor As Integer
With Target
If Intersect(.Cells, Range("v:v")) Is Nothing Then Exit Sub
If IsEmpty(.Cells) Then r.Offset(, 1).Interior.ColorIndex = xlNone: Exit Sub
If Not IsDate(.Cells) Then r.Offset(, 1).Interior.ColorIndex = xlNone: Exit Sub
Select Case Month(.Value)
Case 1: myColor = 3
Case 2: myColor = 17
Case 3: myColor = 19
Case 4: myColor = 22
Case 5: myColor = 26
Case 6: myColor = 33
Case 7: myColor = 36
Case 8: myColor = 38
Case 9: myColor = 40
Case 10: myColor = 42
Case 11: myColor = 44
Case 12: myColor = 7

I have this code in 1 sheet in one of the books (and all other sheets work fine), and the same code in all sheets in the other book. Both books work the way it's supposed to.

My question,
Is it necessary to have the Worksheet_Change event in all the sheets (all sheets act on the code the same way) or is it okay for just one sheet?

Could I encounter a problem if in only one sheet?

I just don't see why I would have to add more size with the code in all sheets if it is not necessary.

View 9 Replies View Related

Is There A Worksheet_Open Event?

Mar 28, 2008

There is a Workbook_Open event in VBA. Is there a Worksheet_Open event? I.e. I want to write some code that is applied when a worksheet becomes active/is displayed to the user.

E.g. I am in Sheet1. I click a button which links me to Sheet2, however in doing that I want the value of the Active Cell in Sheet1 to be displayed in A1 of Sheet2.

Is this possible? Is there another way to do this.

View 9 Replies View Related

On Selection Event

Apr 14, 2008

I would like to create some VBA code that changes the color of the cells I have selected, as soon as I let go of my mouse click. Additionally, is there a way to identify where the range begins and ends so I can test to make sure the range is inside a certain area?

View 9 Replies View Related

Before Save Event

Apr 29, 2008

I want to execute as the user saves the workbook. I want to unhide the rows that may have been hidden during use, on the save. I would prefer it to just happen with no interaction with the user. They save the the book and without them even knowing the rows are unhidden and the file saves. The code it self works as I want it to, I added to a command button with no problems as soon as I add it to the before save in the Thisworkbook it will not even work even if I just try to step in.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Worksheets("Prelims").Range("A11:A511").EntireRow.Hidden = False
Worksheets("Elecs").Range("A11:A1261").EntireRow.Hidden = False
Worksheets("Civils").Range("A11:A5011").EntireRow.Hidden = False
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Worksheets("Civils").Select
End Sub

View 9 Replies View Related

AfterCalculate Event

Jul 10, 2008

I have a macro that needs to run whenever a result from a calculation on my worksheet changes. I'm currently using Worksheet_Change to accomplish this, which works fine.

The only problem is that I would like the macro to wait until all calculations are finished (there's a bunch...) before running the macro. It would seem that the AfterCalculate event would help, but I can't seem to get the code to work correctly (or even at all). I'm pretty new at this... If anyone can give me a clue, I would greatly appreciate it! My searches on the message board and the web have come up pretty empty.

I'm running Excel 2007. Not sure if any other details are needed.

View 9 Replies View Related

Run Before Close Event

Apr 7, 2009

I have a MS Access db in which I have two delete queries called 'qrySessionsCompleted1' and 'qrySessionsCompleted2'. What VBA code can I use in the BeforeClose event to run these?

View 9 Replies View Related

Worksheet Event

May 30, 2009

I would like to run a some code that is only activated if another worksheet in the same workbook is selected and I'm not sure how to achieve it.

View 9 Replies View Related

Event Status

Dec 1, 2009

I have a worksheet that I use to track course scheduling. I have 5 columns which contain dates. At the end I have a Event Status cell that i manually update based on which of my 6 date fields are filled in. I would like to have the Event Status automatically fill with a word as I add dates to the 5 columns. If there is a date in columns 1,2,3, and 4 the event status cell would update based on the date in column 4, dates in columns 1 and 2 - the event status would update based on column 2, etc.

Here are my column headers and what the event status says if there is a date in that column, and no dates in any column to the right of it.

Notional Start Date - Not Contacted
Declined Date - Declined
Contacted Date - Contacted/Working
Scheduled Date - Scheduled
Completion Date - Completed

View 9 Replies View Related

OnZoom Event

Apr 19, 2006

I want to be able to change the size of markers on a chart as the user zooms in and out. I know that I can trap the mouse buttons and check for the zoom that way, but is there an 'OnZoom' event that I can trap in case the user uses the toolbar or menu to zoom?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved