Excel VBA how to populate values from different sheets

Question

I have a sheet that I want to be able to transport cell values being double-clicked by users to another sheet/form called JE.

Example: user clicks on cell F10 in sheet JE, that takes the user to a different sheet (containing a macro), once the user is at that sheet, I want the user to be able to double-click any cell in the A column and the cell value being clicked, fills cell F10 back in sheet JE. So, after being navigated to a macro/different sheet, If the user double clicks on cell A7 and the value "123ABC" is in A7, I would like "123ABC" to populate cell F10 in sheet JE. Similarly, if the user clicks cell F25 on sheet JE, and still chooses to double-click A7 in a different sheet, I would like the value "123ABC" to fill F25 in sheet JE. I would also like the cell in column F to turn to a no-fill background once a value is present in the cell.

The issue I am having now is that my code is set up in such a way that no matter which row the user is using on sheet JE, the values in column A of the macros are only filling the first cell in column F(F7) Whereas, I want the value being double-clicked on in the macro to fill in the F column cell that is active, not the first cell in the F column. Also, when double-clicked, the values in column A of the macros, are not changing the background color of the red cell in column F to a no fill, white background.

This code is the code associated with sheet JE (The main form users are filling out)

Option Explicit
Public sourceRange As Range

Private Sub Worksheet_Change(ByVal Target As Range)
'  If Not Intersect(Target, Range("C7:D446")) Is Nothing Then
      Dim c As Range: Set c = Range("D7:D446")
'     For Each c In Target
 For Each c In c.Cells
          Select Case c.Value
              Case "1000GP", "1000MM", "19FEST", "20IEDU", "20ONLC", "20PART", "20PRDV", "20SPPR", "22DANC", "22LFLC", "22MEDA", "530CCH", "60POUBL", "74GA01", "74GA17", "74GA99", "78REDV"
                  Cells(c.Row, "F").Interior.ColorIndex = 3
              Case Else
                 Cells(c.Row, "F").Interior.ColorIndex = 0
          End Select
      Next c
' End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Set sourceRange = Nothing ' Set it to nothing to avoid too long remembering
  If Target.Column = 6 And Target.Cells.Count = 1 And Target.Interior.ColorIndex = 3 Then
      Set sourceRange = Target ' Remember source cell
      'Cancel = True
      Select Case Target.Offset(0, -2).Value2
        Case "1000GP": gotoref1
        Case "1000MM": gotoref2
        Case "19FEST": gotoref3
        Case "20IEDU": gotoref4
        Case "20ONLC": gotoref5
        Case "20PART": gotoref6
        Case "20PRDV": gotoref7
        Case "20SPPR": gotoref8
        Case "22DANC": gotoref9
        Case "22LFLC": gotoref10
        Case "22MEDA": gotoref11
        Case "530CCH": gotoref12
        Case "60PUBL": gotoref13
        Case "74GA01": gotoref14
        Case "74GA17": gotoref15
        Case "74GA99": gotoref16
        Case "78REDV": gotoref17
      End Select
End If

End Sub

This is the code that lies in all of the macro sheets which the user is brought to when clicking on the cells in the F column in Sheet JE.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Set JE.sourceRange = Target.Value
If Target.Column = 1 Then
   If Not JE.sourceRange Is Nothing Then
        JE.sourceRange.Value = ActiveCell.Value
        Cells(c.Row, "F").Interior.ColorIndex = 0

End If
End If
End Sub

I am also having trouble getting values for the C column to populate multiple rows. For example, if I already have a value in C7 and want to add a value being clicked on in a sheet to the C column as well, If i click on a value, it will overwrite the value in C7 rather than populating cell C8. Here is the code I have for that sheet:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Column = 1 Then
'Sheet2.Range("F" & Target.Row & ":F" & Target.Row).Value = ActiveCell.Value
Sheet2.sourceRange.Value = ActiveCell.Value
Worksheets("JE").Activate
'Sheet2.Activate

'Cells(c.Row, "F").Interior.ColorIndex = 3
Cancel = True
End If
End Sub

Worksheets("JE").Range("F:F").Value = ActiveCell.Value - do you really want to fill that value in the whole column F ? What is ActivateCell here? — Tim Williams
– Tim Williams, Commented Jun 7, 2017 at 19:18
@TimWilliams I don't want to fill it into the whole column. Just the row the user is using. I tried that to see what would happen and it didn't result in what I was looking for. — anve
– anve, Commented Jun 7, 2017 at 19:22
@TimWilliams I'm still having some issues getting cell values being clicked on in a sheet, to populate in the original form. When I try to click on multiple values to populate the C column of the original form, The value just overwrites the first one, staying in cell C7. Do you know how I can get it so if the user clicks on 3 values to be in the C column it will know to go to next available row in the C column? I'll post my code in my question. Thanks for any help! — anve
– anve, Commented Jun 9, 2017 at 13:45
C column, or F column? If you want to be able to fill down by clicking multiple values then whenever the user triggers the double-click use something like Set sourceRange = sourceRange.Offset(1, 0) to move the destination cell down one row — Tim Williams
– Tim Williams, Commented Jun 9, 2017 at 16:42
@TimWilliams The C column. I inputted that code under the double-click event and it didn't seem to work. I was hoping to be able to add many values in the C column (C7:C446). So, if I already have a value in cell C7 and I want to also add more values to the C column, I am able to. As of right now, I can only add values to the C7. I'm unsure of the syntax to execute that or where to put that snip it of code. Any additional help would be awesome. I realize its probably a small fix but I am not familiar with VBA at all. — anve
– anve, Commented Jun 9, 2017 at 18:37

smartobelix · Accepted Answer · 2017-06-07 20:51:07Z

1

You need to store somwhere original Range active before user switch to different worksheet. Then you will be able to write there value.

How to do it:

1) Add public variable on worksheet JE (just after line Option Explicit)

Public sourceRange As Range

2) In event Worksheet_SelectionChange on worksheet JE add just after first if code to set variable to current Range - please also notice first line with setting variable to Nothing - it is required to avoid remembering last range forever:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Set sourceRange = Nothing ' Set it to nothing to avoid too long remembering
  If Target.Column = 6 And Target.Cells.Count = 1 And Target.Interior.ColorIndex = 3 Then
      Set sourceRange = Target ' Remember source cell
      'Cancel = True

3) Now it is easy - just use variable in double click event on other sheet:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Column = 1 Then
    If Not JE.sourceRange Is Nothing Then
        JE.sourceRange.Value = ActiveCell.Value
    End If

Please note rest of code should stay like it is - for clarity I put only fragments where you need to change something.

answered Jun 7, 2017 at 20:51

smartobelix

7485 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

anve Over a year ago

When I go to double-click the values in the A column in "other Sheet", in the macros, I get an Object-Required for the If Not JE.sourceRange Is Nothing Then statement. Any idea why? My guess is because the JE.sourceRange hasn't been set to anything?

anve Over a year ago

How can the code in the macros ("Other Sheets") change the color of the F column cells in the JE sheet? The code I edited in my original post show how I've tried to do that. Could you provide feedback as to how I can go about that?

smartobelix Over a year ago

JE.sourceRange.Cells.Interior.ColorIndex = 0. If you have Object -Required message for line If Not JE.sourceRange Is Nothing Then means your JE Sheet has different name - look in VBA editor how it is named in VBA - after typing JE and dot Intelli Sense should advice sourceRange

braX · Accepted Answer · 2017-06-07 19:18:54Z

0

Have you tried using the Target argument? It will tell you the row and column that is being double clicked, and you can use that to construct the range more specifically.

Worksheets("JE").Range("F" & Target.Row & ":F" & Target.Row).Value = ActiveCell.Value

answered Jun 7, 2017 at 19:18

braX

11.9k5 gold badges24 silver badges38 bronze badges

1 Comment

anve Over a year ago

should the code inside the macro sheets be what I just added to my original code?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)  If Target.Column = 1 Then Worksheets("JE").Range("F" & Target.Row & ":F" & Target.Row).Value = ActiveCell.ValueCancel = True End If End Sub

Collectives™ on Stack Overflow

Excel VBA how to populate values from different sheets

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related