Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I wonder whether someone may be able to help me please.

I'm using the code below to autopopulate a number of columns with pre-defined text when a value is entered into column "B".

Option Explicit
Public preValue As Variant


    Private Sub Worksheet_Change(ByVal Target As Range)

        Dim cell As Range, res As Variant
        Dim FirstBlankCell As Range
        Dim lr As Long
        Dim msg
        Dim rCell As Range
        Dim Rng As Range, Dn As Range
        Dim Rng1 As Range
        Dim Rng2 As Range
        Dim Rng3 As Range
        Dim Rng4 As Range
        Dim Rw As Range


        If Target.Cells.Count > 1 Then Exit Sub

        lr = lr


        With Target
            Select Case True

            Case .Column = 2
            If .Value2 > 0 And .Value2 <> "Enter your name" And .Offset(, -1) = "" Then
                    .Offset(, 1).Value2 = "Yes"
                    .Offset(, 2).Value2 = "--Select--"
                    .Offset(, 3).Value2 = "--Select--"
                    .Offset(, 4).Value2 = "--Select--"
                    .Offset(, 5).Value2 = "Enter your FTE"
                    .Offset(, 6).Value2 = "C&R"
                    .Offset(, 7).Value2 = "--Select--"
                    .Offset(, 17).Value2 = "Enter the name of your Line Manager"
                  End If
            Case Else
            End Select
        End With

    End Sub

This works fine, but I'd now like to extend this functionality a little further. I'd also like the population of columns to occur if values are copied and paste into column "B" in addition to when they are entered manually.

This will help when I migrate the information from exisitng sheets into the new one I'm building. Although this is the last change I need to make, it's proving the hardest to find a solution for.

I just wondered whether someone could possibly look at this please and offer some guidance on how I may go about achieving this.

Many thanks and kind regards

Chris

share|improve this question

2 Answers 2

up vote 1 down vote accepted

As per Karen, the event does fire if contents are cut and pasted. The thing is, the code you posted checks to see if the number of cells returned is greater than one, and if so, it stops the event from executing:

If Target.Cells.Count > 1 Then Exit Sub

Change the code to this, and it should work just fine.

Dim cell As Range, res As Variant
Dim FirstBlankCell As Range
Dim lr As Long
Dim msg
Dim rCell As Range
Dim Rng As Range, Dn As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Rng4 As Range
Dim Rw As Range

lr = lr

Dim r As Range
For Each r In Target.Cells
With r
    Select Case True

    Case .Column = 2
    If .Value2 > 0 And .Value2 <> "Enter your name" And .Offset(, -1) = "" Then
            .Offset(, 1).Value2 = "Yes"
            .Offset(, 2).Value2 = "--Select--"
            .Offset(, 3).Value2 = "--Select--"
            .Offset(, 4).Value2 = "--Select--"
            .Offset(, 5).Value2 = "Enter your FTE"
            .Offset(, 6).Value2 = "C&R"
            .Offset(, 7).Value2 = "--Select--"
            .Offset(, 17).Value2 = "Enter the name of your Line Manager"
          End If
    Case Else
        End Select
    End With
Next r

What was done here was that line of code was removed to allow the event to fire even if more cells that one are in the target range, and then we use a For Each loop on the cells contained in the Target to work on each row.

Nim

share|improve this answer
    
Hi @Nim, thank you for taking the time to reply to my post. The solution works gereat. Kind regards. Chris –  IRHM Feb 11 '13 at 18:09

The Worksheet_Change(ByVal Target As Range) event is fired by both manual entry and pasting of data, so it should just work. Did you try it?

share|improve this answer
    
Hi @Karan, thank you for taking the time to reply to my post, and my apologies for not replying sooner. I've done some more testing around this, and I've found out that if I copy and paste into one row, the population of the columns works. However if my paste range is greater than this, the code no longer works. Many thanks and kind regards Chris –  IRHM Feb 10 '13 at 15:15
    
Just saw your comment, but seems nim addressed the issue before I could get to it. –  Karan Feb 11 '13 at 12:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.