Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Problem
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Problem
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 "RVS" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Problem
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 Sub You 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 "RVS" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Problem. | Excel Programming | |||
Variable problem | Excel Programming | |||
Having a problem using a variable to add a 'name' | Excel Programming | |||
Having a problem using a variable to add a 'name' | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |