How to get worksheet_selection change to work

[mkvarious]

hello,

I need a help with the following task: when a cell from H column is changed then I need a formula in I cell column to be written. Obviously I do not want to use Excel formulas like: =if(H1="","",weeknum(H1)) or any other variation of any formula as the file needs to be used by various users and as usual in such instances they will/can delete the formula etc. etc.

So I need VBA code that reacts to workbook events. I have tried many variations and still did not get what I am looking for. not sure, but think that my problem is that I narrow the SELECTION CHANGE to column H only and then it does not always work. I am even using SHEET CHANGE to get the expected result but am not getting it at right.

below (one) of the variation codes I have been trying. since I have limited SELECTION CHANGE and SHEET CHANGE to check whether H column is empty or not, then I realized that:

• when I input data in H column to na empty cell and press enter they (SELECTION CHANGE/SHEET CHANGE) do nothing
• when I input data in H column to na empty cell and press tab they do nothing
• when I input data in H column to na empty cell and press ctrl + enter one of them works (SHEET CHANGE works) and writes the formula in I cell
• when I use cursors to get to a cell with data in H column then it works (SELECTION CHANGE works) and writes the formula in I cell

what I need is: when data has been inputted in H cell, then a formula needs to be written in I cell (forget the formula now, I need the event to work, first). I need this/these events to work when cells in H columns have been changed by:

• pressing enter
• pressing tab
• using cursors
• pressing ctrl + tab

since I am clueless how to get that working can someone help me, please?
do I need two events or just one, but properly written?

cheers,
mkvarious

msgbox is obviously used for tracking purposes, only

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim który As Long

If Target.Column = 8 Then
If ActiveCell.Value <> "" Then
który = ActiveCell.Row
MsgBox "selection change"
MsgBox który
ActiveCell.Offset(0, 1).Formula = "=weeknum(" & Cells(ActiveCell.Row, "H") & ",2)"
End If
End If
End sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim który As Long

If Target.Column = 8 Then
If ActiveCell.Value <> "" Then
który = ActiveCell.Row
MsgBox "sheet change"
MsgBox który
ActiveCell.Offset(0, 1).Formula = "=weeknum(" & Cells(ActiveCell.Row, "H") & ",2)"
End If
End Sub

 

[Michael M]

try changing this

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

to

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)

AND
Make sure it is pasted into the sheet module you are working with not a Standard or This Workbook module !!

 

[mkvarious]

hello Michael M,

thanks for your reply. it has not changed, though.
I changed

  Private Sub WorkSheet_SelectionChange(ByVal Target As Range)

according to your suggestion and moved it to Sheet1 but the result is the same as it was before.
any second thought?

mkvarious

 

[mkvarious]

finally have made it!
inspiration has come mainly from the thread [URL]http://www.mrexcel.com/forum/excel-questions/552778-worksheet-selectionchange.html[/url] and Smitty's piece of advice

You use a Change event to test if a cell's value has changed. The SelectionChange event is fired based on movement around the worksheet. He's some boilerplate Change event code. Check the comments for where to put your range:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("xxx")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here...)

End Sub


HTH,

but obviously to Miachel M as well as I was unaware WorkSheet_Change or WorkSheet_SelectionChange should be contained within the sheet module.
also I now know that what I needed was WorkSheet_Change not WorkSheet_SelectionChange.
the main problem was how to properly define H column as range but we learn each day

the code goes like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim który As Long
Set rng = Columns(8)
If Intersect(Target, rng) Is Nothing Then Exit Sub
    który = ActiveCell.Row
    MsgBox "sheet change"
    MsgBox który
    Cells(który, 8).Offset(0, 1).Formula = "=weeknum(" & Cells(który, 8) & ",2)"
End Sub

now my problem is to properly translate date to VBA to be used in WEEKNUM formula but it should be way easier!

mkvarious

 

[mkvarious]

error in posting, credit goes to:

http://www.mrexcel.com/forum/excel-questions/552778-worksheet-selectionchange.html

Smitty

thanks again