Newbie Question -- Capturing Cell Input

[ChewDoggie]

Hello All,

Although I've developed a TON of VB6 code in my life and a good share of C#/VB.NET code, this is my first venture into Excel VBA code.

HR is on my back to finish a VB.NET application I've been working on for 3 months and they want it NOW. Yesterday, I found out that all they really need is a way for the existing Excel Spreadsheet they're using to accurately perform some calculations and they'll get off my back about the .NET app for now (we'll see).

So...I've been given an assignment to modify an Excel spreadsheet that has 5 or 6 dropdown lists on it. The datasheet is used by HR to keep track of Employee attendance.

My VBA Newbie questions are as follows:

1) How do I capture values entered by a user in a cell?

2) In my Visual Basic Editor (Alt+F11), I can see "Sheet" objects and some limited events. Are there other objects besides "Sheet" and are there other events, besides the ones associated with "Sheet" ?

My goal is to be able determine when the focus has left a certain cell, and then capture data from that cell (et al) and do some calcuations with them.

I've created this code so far just to experiment with code I've seen online, but it barfs with "Unable to set the Text Property of the Range class" (and I don't think its behaving as I intend).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim EnteredReason As String
    
    If ActiveSheet.Range("K1").Select = True Then
        EnteredReason = ActiveSheet.Range("K1").Text
        ActiveSheet.Range("L1").Text = EnteredReason
    End If
End Sub

BTW, if anyone knows of a good online site (beside a basic Google search) that'll keep me busy for a while, I'd love that too.

Thanks to ALL.

Chew

10% of your life is what happens to you. 90% of your life is how you deal with it.

 

[SkipVought]

Hi,

1) How do I capture values entered by a user in a cell?

I'd use the Worksheet_Change event.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 the Exit Sub  'user could PASTE a range in here
    If Not Intersect(Range"K1"), Target) is  Nothing Then
        Range("L1").Value = Target.Value
    End If
End Sub

Since the event assumes the sheet is active, activesheet is assumed as well as ALL range references in this code. If you were referencing ranges on some other sheet, definitely use THAT sheet reference.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ChewDoggie]

Thanks, Skip. You banged out that code pretty fast. My example captures input on Column "K", ROW #1. How do I capture Column "K", EDITED ROW (which could be any row) ?

THanks !

Chew


10% of your life is what happens to you. 90% of your life is how you deal with it.

 

[ChewDoggie]

Sorry, I found it:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Integer
    Dim CellRange As String
    If Target.Count > 1 Then
        Exit Sub  'user could PASTE a range in here
    End If
    CellRange = "K" & Target.Row
    If Not Intersect(Range(CellRange), Target) Is Nothing Then
        CellRange = "L" & Target.Row
        Range(CellRange).Value = Target.Value
    End If
End Sub

Thanks !

Chew

10% of your life is what happens to you. 90% of your life is how you deal with it.

 

[SkipVought]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then
        Exit Sub  'user could PASTE a range in here
    End If
    If Not Intersect(Range("K1").EntireColumn, Target) Is Nothing Then
        Cells(Target.Row, "L").Value = Target.Value
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Target.Column = 11 Then 'Column K
    Application.EnableEvents = False
    Target.Offset(0, 1).Value = Target.Value
    Application.EnableEvents = True
  End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886