Value of the "Last Cell"
 

This is what I want to do;
I have this spreadsheet that is going to be used for updating data on
an Outlook Form.
When the spreadsheet opens the user clicks a button that will import
the Outlook Items from the public folder into the spreadsheet. This
will also bring in the EntryID for each Item. Now the user can edit
these items.

Ex… Lets say I am in Cell B2 and I type in "Tony", when I leave Cell
B2 and go to B3 my code will execute and take the value of Cell B10
(which happens to be my entryID) and the value of B2 ("Tony") and put
it in the First Name field on the Outlook Form that matches that
entryID and storeID.

Here are the issues I am experencing now:
1. I am not sure how to reference the previous cell that was edited.
I have tried using SpecialCells(xlCellTypeLastCell) but this doesn't
seen to work when you click the delete key while your on a cell.
2. I also need to reference the last cell that is on the same row as
the previouse cell. This is pretty much the same problem as #1 but I
thought it was worth mentioning.
3. I am unsure of where the best place to collect this data
a. Worksheet_Change()
b. Worksheet_SelectionChange()
c. Workbook_SheetSelectionChange()

Any suggestions or help would be greatly appreciated.

‘This is the code I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)

Set myOlApp = CreateObject("Outlook.Application")
Set MyNameSpace = myOlApp.GetNamespace("MAPI")
Set PublicFolders = MyNameSpace.Folders("Public Folders")
Set AllPublicFolders = PublicFolders.Folders("All Public Folders")
Set Folders1 = AllPublicFolders.Folders("Public Folder 1")
Set XFld = Folders1.Folders("XFld")
Set Items = Fld.Items

Dim xCol As Variant
Dim storeID As String
Dim entryID As String
Dim LeftCell As Range
Dim RightCell As Range

‘HERE I AM USING SpecialCells(xlCellTypeLastCell) TO GET THE LAST
CELL
‘AND End(xlToRight) TO GET THE GET THE LAST CELL ON THAT ROW WHICH
HOLDS THE entryID
x = ActiveCell.SpecialCells(xlCellTypeLastCell).End(xl ToRight)

entryID = X
storeID = XRef.storeID

Set XRefItem = MyNameSpace.GetItemFromID(entryID, storeID)

‘A TEST TO SEE IF I AM GETTING RIGHT RECORD
MsgBox XRefItem.UserProperties("StockNum")

‘This is where it puts it in the form
xCol = Target.Column

If xCol = 1 Then
'xRef.UserProperties("Field1") = Target
Else
If xCol = 2 Then
'xRef.UserProperties("Field2") = Target
Else
If xCol = 3 Then
'xRef.UserProperties("Field3") = Target
Else
End If
End If
End If


Set XRefItem = Nothing
Set Items = Nothing
Set XRef = Nothing
Set ddFolders = Nothing
Set AllPublicFolders = Nothing
Set PublicFolders = Nothing
Set MyNameSpace = Nothing
Set myOlApp = Nothing

End Sub

Value of the "Last Cell"
 

Tony,
Yeah, the Target tells you where you are going, not where you've been.
This might work for you...

Private Static Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngCell As Range
Dim varValue As Variant
On Error Resume Next

If varValue < rngCell.Value Then
If Err = 0 Then
MsgBox rngCell.Row
End If
End If
Set rngCell = ActiveCell
varValue = rngCell.Value
End Sub

One drawback, if the active cell is edited as soon as the workbook is
opened, the initial value in the code does not have a chance to initialize.
So an Auto_Open routine should run that would change the selection to a
different cell, thus executing the code above.

This code also does not work if a user edits a cell and immediately switches
worksheets, workbooks, or leaves Excel for another application.

Dale Preuss



"Tony" wrote:

This is what I want to do;
I have this spreadsheet that is going to be used for updating data on
an Outlook Form.
When the spreadsheet opens the user clicks a button that will import
the Outlook Items from the public folder into the spreadsheet. This
will also bring in the EntryID for each Item. Now the user can edit
these items.

Ex€¦ Lets say I am in Cell B2 and I type in "Tony", when I leave Cell
B2 and go to B3 my code will execute and take the value of Cell B10
(which happens to be my entryID) and the value of B2 ("Tony") and put
it in the First Name field on the Outlook Form that matches that
entryID and storeID.

Here are the issues I am experencing now:
1. I am not sure how to reference the previous cell that was edited.
I have tried using SpecialCells(xlCellTypeLastCell) but this doesn't
seen to work when you click the delete key while your on a cell.
2. I also need to reference the last cell that is on the same row as
the previouse cell. This is pretty much the same problem as #1 but I
thought it was worth mentioning.
3. I am unsure of where the best place to collect this data
a. Worksheet_Change()
b. Worksheet_SelectionChange()
c. Workbook_SheetSelectionChange()

Any suggestions or help would be greatly appreciated.

€˜This is the code I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)

Set myOlApp = CreateObject("Outlook.Application")
Set MyNameSpace = myOlApp.GetNamespace("MAPI")
Set PublicFolders = MyNameSpace.Folders("Public Folders")
Set AllPublicFolders = PublicFolders.Folders("All Public Folders")
Set Folders1 = AllPublicFolders.Folders("Public Folder 1")
Set XFld = Folders1.Folders("XFld")
Set Items = Fld.Items

Dim xCol As Variant
Dim storeID As String
Dim entryID As String
Dim LeftCell As Range
Dim RightCell As Range

€˜HERE I AM USING SpecialCells(xlCellTypeLastCell) TO GET THE LAST
CELL
€˜AND End(xlToRight) TO GET THE GET THE LAST CELL ON THAT ROW WHICH
HOLDS THE entryID
x = ActiveCell.SpecialCells(xlCellTypeLastCell).End(xl ToRight)

entryID = X
storeID = XRef.storeID

Set XRefItem = MyNameSpace.GetItemFromID(entryID, storeID)

€˜A TEST TO SEE IF I AM GETTING RIGHT RECORD
MsgBox XRefItem.UserProperties("StockNum")

€˜This is where it puts it in the form
xCol = Target.Column

If xCol = 1 Then
'xRef.UserProperties("Field1") = Target
Else
If xCol = 2 Then
'xRef.UserProperties("Field2") = Target
Else
If xCol = 3 Then
'xRef.UserProperties("Field3") = Target
Else
End If
End If
End If


Set XRefItem = Nothing
Set Items = Nothing
Set XRef = Nothing
Set ddFolders = Nothing
Set AllPublicFolders = Nothing
Set PublicFolders = Nothing
Set MyNameSpace = Nothing
Set myOlApp = Nothing

End Sub

Value of the "Last Cell"
 

Tony

Target is the cell that was changed when you use Worksheet_Change. It
doesn't matter what the ActiveCell is, Target points to the cell that fired
the event. SpecialCells(xlCellTypeLastCell) returns the last cell in the
worksheet, so you don't want to use that.



'HERE I AM USING SpecialCells(xlCellTypeLastCell) TO GET THE LAST
CELL
'AND End(xlToRight) TO GET THE GET THE LAST CELL ON THAT ROW WHICH
HOLDS THE entryID
x = ActiveCell.SpecialCells(xlCellTypeLastCell).End(xl ToRight)

x = Target.End(xlToRight).Value

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com