Variable Problem

[RVS]

Here is another riddle for all of you...

I am writing a program that will recognize what row the user is inputting
data into and will store that row number as variable nRow. I have declared
this as a public variable in another module as I need the whole program to be
able to access it. I included a message box in my code in order to make sure
that my program is finding the correct row. My problem is whenever I enter
new data, the message box that appears indicates row 1 column 2. I suppose
this makes sense as I have Cells(1,2) throughout the next major chunk of
code. I tried pasting my nRow code after all of the code the references
Cells(1,2) but still no success. Here is what I've got...


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



'Determine which row has a new client activity

With Target(1)
nRow = .Row
nCol = .Column
sAddr = .Address
End With
nRow = Target(1).Row
MsgBox "row: " & nRow & vbCr & _
"col: " & nCol & vbCr & _
sAddr

'If data is entered into Columns 6,7,8,9,10,11, or 12 then update the
Time/Date Stamp

If Target.Column = 6 Then
Cells(1, 2).Value = Now

ElseIf Target.Column = 7 Then
Cells(1, 2).Value = Now

ElseIf Target.Column = 8 Then
Cells(1, 2).Value = Now

ElseIf Target.Column = 9 Then
Cells(1, 2).Value = Now

ElseIf Target.Column = 10 Then
Cells(1, 2).Value = Now

ElseIf Target.Column = 11 Then
Cells(1, 2).Value = Now

ElseIf Target.Column = 12 Then
Cells(1, 2).Value = Now

End If


'Fill Month ListBox before dialog box appears
With UserForm1.ComboBox1
.RowSource = ""
.AddItem "Jan"
.AddItem "Feb"
.AddItem "Mar"
.AddItem "Apr"
.AddItem "May"
.AddItem "Jun"
.AddItem "Jul"
.AddItem "Aug"
.AddItem "Sep"
.AddItem "Oct"
.AddItem "Nov"
.AddItem "Dec"
End With

'Fill Week Number ListBox before dialog box appears

With UserForm1.ComboBox2
.RowSource = ""
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
End With
UserForm1.Show


End Sub


Any help would be much appreciated! Thanks!

 

[Harald Staff]

Hi

Without testing (it's way past midnight here and a very hot summer night
with cold beer),
Cells(1, 2).Value = Now
will trigger the Workbook_SheetChange event. You may not want that. Turn it
off by Application.EnableEvents = False, or as I prefer, set a boolean value
to true and abort the event code action if true.
Note also that Target.Column vill fail if pasting multiple cells, switch to
Target(1).Column, maybe also abort code if Target.Count > 1.

HTH. Best wishes Harald

 

[ker_01]

RVS-

I apologize, as I don't fully comprehend your question (what scenarios are
resulting in unexpected outcomes). However, one observation is that anytime
you update a cell in columns 6 through 12, it will be placing a value in cell
(1,2), which I would expect to trigger *another* workbook_sheetchange event
(or... worksheet_change event?)

So, the behavior I'd expect (without testing) is that it would msgbox your
original cell, then as soon as you reply to that msgbox, if you changed
column 6-12, it would pop up with another messagebox right away saying (1,2)

HTH,
Keith

 

[Patrick Molloy]

add one line after the SUB declaration

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

If Target.Address = Cells(1,2).Address then Exit SubYou understand that, from other replies, when the timestamp is saved in B1
it also triggers the event...so this line of code says if its the
timestamp, exit as we don't care...


also reduce your if statements to this

If ( Target.Column >= 6 ) AND ( Target.Column <= 12 ) Then
Cells(1, 2).Value = Now
End If