Variable Problem
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
"RVS" wrote:
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!
|