Dynamic Cell Editing from Another Sheet

[mariah3]

I'm brand new to VBA and Macros, having had the luxury of relying on statistical programming languages like R and Python up until now. Due to some restrictions at my workplace that won't allow me to use R Shiny or Python Flask for the task at hand, I am looking to make what is effectively a data review tool in excel. I'll add that access to MS Forms and Power Apps is coming down the pipeline but not in time. I'm pursuing other options using PDF and such for my immediate need but am hopeful this can work in excel.

For the sake of an easy-to-relay problem set (posted below as mini sheets), let's say I have a hidden 'sheet1' as my back-end data with an ID (col A, numeric), Value1 (col B, string), and Comments (col C, string); and 'sheet2' as my front-end for the reviewers I'm working with, which contains a drop-down to select an ID from sheet1 to display the associated Value1 and let a reviewer type in their comment. Their comment would then fill the corresponding row of sheet1!Comments. Now for the tricky part: my reviewers want the ability to save and then come back to edit their comments. This would require some method to "read in" a value from sheet1!Comments when the dropdown is selected in sheet2, allow the reviewer to edit that comment in sheet2, and then overwrite the sheet1!Comments cell to update their comment.

Is this even possible? If so, I'm open to whatever makes this a smooth experience for the reviewers -- Macros, VBA buttons to 'Save' into sheet1 or even update a formula that contains their comment as text in part of the formula, even something like keeping the comments in sheet2 in hidden rows that hide/unhide based on the dropdown selection. I just have to get all of the reviewer files back and collate their comments, so I don't care which sheet their comments are saved in so long as I can identify which ID and Value# it is associated with as there are multiple elements per ID for them to review in reality.

Regards,
2 Math Degrees & 1 Excel Headache


SHEET1 (Back-end data to be hidden from reviewers):

data_editing_example.xlsx
	A	B	C
1	ID	Value1	Comments
2	1	aaa	Reviewer write and edit comments here
3	2	bbb	FALSE
4	3	ccc	FALSE
5	4	ddd	FALSE
6	5	eee	FALSE
sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=IF(sheet2!B3=sheet1!A2,sheet2!B7)
C5:C6	C5	=IF(sheet2!B7=sheet1!A5,sheet2!B10)

SHEET2 (Front-End for Reviewer Interaction/Editing):

data_editing_example.xlsx
	A	B	C	D
1	REVIEWER FORM
2
3	Select ID:	1
4
5	Value to Review:	aaa
6
7	Comments from Reviewer:	Reviewer write and edit comments here
8
sheet2

Cell Formulas
Range	Formula
B5	B5	=INDIRECT("sheet1!B"&MATCH($B$3,sheet1!A2:sheet1!A6)+1)

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=sheet1!$A$2:$A$6

 

[Cubist]

A Couple of recommendations:
1) Avoid using INDIRECT() as it is volatile and slow. In Sheet2, cell B5 replaces with this formula instead.

=VLOOKUP(B3,Sheet1!A2:B6,2)

2) Avoid merged cells if possible. People on here run away from it like a plague. Use "Center across selection instead"
First, unmerge your cells in B7. Select B7 and C7 -> Ctrl 1 -> Alignment -> Horizontal -> Center accros selection
3) Right-click sheet2 name -> View code -> Paste this into the editor. This macro retrieves the last comment. It triggers when there's a change in cell B3.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rngData As Range
    Dim lastRow As Long

    If Not Intersect(Target, Me.Range("B3")) Is Nothing And Target.Worksheet.Name = "Sheet2" Then
        Set ws1 = ThisWorkbook.Sheets("Sheet1")
        Set ws2 = ThisWorkbook.Sheets("Sheet2")
     
        Application.EnableEvents = False
        With ws1
            Set rngData = .Range("A1").CurrentRegion
            rngData.AutoFilter Field:=1, Criteria1:=ws2.Range("B3").Value
            On Error Resume Next
            lastRow = rngData.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 3).Row
            On Error GoTo 0
        
            If lastRow > 1 Then ws2.Range("B7").Value = .Range("C" & lastRow)
        
        End With
        Application.EnableEvents = True
    End If
End Sub

4) Create a shape on Sheet1 and assign this macro to it. This macro updates the comment in Sheet1.

Sub UpdateComments()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim newValue As Variant
    Dim lastRow As Long
    Dim rngData As Range

    Application.EnableEvents = False
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")

        With ws1
            Set rngData = .Range("A1").CurrentRegion
            rngData.AutoFilter Field:=1, Criteria1:=ws2.Range("B3").Value
            On Error Resume Next
            lastRow = rngData.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 3).Row
            On Error GoTo 0
           .Range("C" & lastRow).Value = ws2.Range("B7").Value
        End With
    Application.EnableEvents = True
End Sub

 

[mariah3]

A Couple of recommendations:
1) Avoid using INDIRECT() as it is volatile and slow. In Sheet2, cell B5 replaces with this formula instead.

=VLOOKUP(B3,Sheet1!A2:B6,2)

2) Avoid merged cells if possible. People on here run away from it like a plague. Use "Center across selection instead"
First, unmerge your cells in B7. Select B7 and C7 -> Ctrl 1 -> Alignment -> Horizontal -> Center accros selection
3) Right-click sheet2 name -> View code -> Paste this into the editor. This macro retrieves the last comment. It triggers when there's a change in cell B3.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rngData As Range
    Dim lastRow As Long

    If Not Intersect(Target, Me.Range("B3")) Is Nothing And Target.Worksheet.Name = "Sheet2" Then
        Set ws1 = ThisWorkbook.Sheets("Sheet1")
        Set ws2 = ThisWorkbook.Sheets("Sheet2")
    
        Application.EnableEvents = False
        With ws1
            Set rngData = .Range("A1").CurrentRegion
            rngData.AutoFilter Field:=1, Criteria1:=ws2.Range("B3").Value
            On Error Resume Next
            lastRow = rngData.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 3).Row
            On Error GoTo 0
       
            If lastRow > 1 Then ws2.Range("B7").Value = .Range("C" & lastRow)
       
        End With
        Application.EnableEvents = True
    End If
End Sub

4) Create a shape on Sheet1 and assign this macro to it. This macro updates the comment in Sheet1.

Sub UpdateComments()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim newValue As Variant
    Dim lastRow As Long
    Dim rngData As Range

    Application.EnableEvents = False
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")

        With ws1
            Set rngData = .Range("A1").CurrentRegion
            rngData.AutoFilter Field:=1, Criteria1:=ws2.Range("B3").Value
            On Error Resume Next
            lastRow = rngData.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 3).Row
            On Error GoTo 0
           .Range("C" & lastRow).Value = ws2.Range("B7").Value
        End With
    Application.EnableEvents = True
End Sub

Thank you, this has been very helpful! I've spent the last few days messing around with the functions you recommended and provided to make sure I grasp the concept and execution in various contexts. In an effort to understand the code better, I've been going through each of the lines to make sure I truly understand what it does. One line that has me caught up is how you define the variable lastRow in part (4) of your response. This is the line before setting the Error action and finally pointing the appropriate cell in Sheet1 to the cell we need it to copy from Sheet2.

Overall I understand that this line is adjusting the "last" row to be what's visible because the function filters the Sheet1 data table to hide every row in Sheet1 that doesn't match the ID we've selected in the form on Sheet2, but I don't quite understand what two parts of the lastRow definition mean or do. Could you please explain a little more about how rngData.Offset() works within this context? I want to say the offset piece is narrowing it down to 1 cell or 1 row, but I'm not entirely sure that I'm thinking about it correctly. Also, you specified .Cells(1,3) -- what do the 1 and 3 refer to? Is that the 1st row (excluding column headers) and the 3rd column of the visible data in Sheet1?

 

[Cubist]

rng.Data.Offset(1) Shifts the range down by one row to exclude the header row.
.SpecialCells(xlCellTypeVisible) selects only visible cells in the filtered range.
.Cells(1,3)returns the row number of the first visible cell which is row 1 (hence the 1) in column C (hence the 3)