shp025
02-27-2017, 09:18 AM
I am trying to create a spreadsheet in which you can input data into a database and for it to make the same changes in individual sheets. You should also be able to go to individual sheets and for the spreadsheet to automatically make changes in the database. Like a two way input and output. While googling for answers, I've seen it be called "Mirroring Cells".
So I've gotten individual pairs of cells to mirror, but it takes about 9 lines of code to achieve it.
If Target = wksData.Range("C1") ThenApplication.EnableEvents = False
wksC.Range("B9").Value = wksData.Range("C1")
Application.EnableEvents = True
ElseIf Target = wksC.Range("B9") Then
Application.EnableEvents = False
wksData.Range("C1").Value = wksC.Range("B9").Value
Application.EnableEvents = True
End If
I couldn't figure out a way to loop the code in part because the cell location in the database and in the individual spreadsheets are a little random. I need them to be in that specific format I am told because of readability and printability. So I thought I would use a lookup table that would help me pull up the corresponding cell location from the database to the sheet and vice versa.
However, I've been getting a Runtime Error on two things in my code.
One for "If Not Intersect(Target,wksData.Range("A1:E225")) is Nothing Then"
And for when I set the corresponding cell with the target cell.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim wksData As Worksheet, wksCurrent As Worksheet, wksLocRef As Worksheet
'Active sheet cell row and column
Dim irownum As Long, icolnum As Long
'Database sheet cell row and column
Dim drownum As Long, dcolnum As Long
'Reference sheet tab location cell row and column
Dim trownum As Long, tcolnum As Long, tsheetnum As Long
Set wksData = Worksheets("Database")
Set wksLocRef = Worksheets("Location Reference")
Set wksCurrent = Worksheets(ActiveSheet.Name)
irownum = Target.Row
icolnum = Target.Column
'If change in Database...
If Not Intersect(Target, wksData.Range("A1:E225")) Is Nothing Then
'Place row and column numbers in cells from the Location Reference sheet
Application.EnableEvents = False
wksLocRef.Range("G3") = irownum
wksLocRef.Range("G4") = icolnum
Application.EnableEvents = True
'Index and Match formula in the sheet will determine the corresponding cell location
'Receive new values into macro
trownum = wksLocRef.Range("G6").Value
tcolnum = wksLocRef.Range("G7").Value
tsheetnum = wksLocRef.Range("G8").Value
'Set cells equal to each other
Worksheets(tsheetnum).Range(Cells(tcolnum, trownum)).Value = Target.Value
Else
Application.EnableEvents = False
wksLocRef.Range("G12").Value = icolnum
wksLocRef.Range("G13").Value = irownum
Application.EnableEvents = True
drownum = wksLocRef.Range("G15").Value
dcolnum = wksLocRef.Range("G16").Value
wksData.Range(Cells(drownum, dcolnum)).Value = Target.Value
End If
End Sub
I'm still new to VBA and I don't know what prompted these errors, if this is syntax or a fundamentally wrong way of going about the problem. I would appreciate any help and assistance. Thank you!
18498
So I've gotten individual pairs of cells to mirror, but it takes about 9 lines of code to achieve it.
If Target = wksData.Range("C1") ThenApplication.EnableEvents = False
wksC.Range("B9").Value = wksData.Range("C1")
Application.EnableEvents = True
ElseIf Target = wksC.Range("B9") Then
Application.EnableEvents = False
wksData.Range("C1").Value = wksC.Range("B9").Value
Application.EnableEvents = True
End If
I couldn't figure out a way to loop the code in part because the cell location in the database and in the individual spreadsheets are a little random. I need them to be in that specific format I am told because of readability and printability. So I thought I would use a lookup table that would help me pull up the corresponding cell location from the database to the sheet and vice versa.
However, I've been getting a Runtime Error on two things in my code.
One for "If Not Intersect(Target,wksData.Range("A1:E225")) is Nothing Then"
And for when I set the corresponding cell with the target cell.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim wksData As Worksheet, wksCurrent As Worksheet, wksLocRef As Worksheet
'Active sheet cell row and column
Dim irownum As Long, icolnum As Long
'Database sheet cell row and column
Dim drownum As Long, dcolnum As Long
'Reference sheet tab location cell row and column
Dim trownum As Long, tcolnum As Long, tsheetnum As Long
Set wksData = Worksheets("Database")
Set wksLocRef = Worksheets("Location Reference")
Set wksCurrent = Worksheets(ActiveSheet.Name)
irownum = Target.Row
icolnum = Target.Column
'If change in Database...
If Not Intersect(Target, wksData.Range("A1:E225")) Is Nothing Then
'Place row and column numbers in cells from the Location Reference sheet
Application.EnableEvents = False
wksLocRef.Range("G3") = irownum
wksLocRef.Range("G4") = icolnum
Application.EnableEvents = True
'Index and Match formula in the sheet will determine the corresponding cell location
'Receive new values into macro
trownum = wksLocRef.Range("G6").Value
tcolnum = wksLocRef.Range("G7").Value
tsheetnum = wksLocRef.Range("G8").Value
'Set cells equal to each other
Worksheets(tsheetnum).Range(Cells(tcolnum, trownum)).Value = Target.Value
Else
Application.EnableEvents = False
wksLocRef.Range("G12").Value = icolnum
wksLocRef.Range("G13").Value = irownum
Application.EnableEvents = True
drownum = wksLocRef.Range("G15").Value
dcolnum = wksLocRef.Range("G16").Value
wksData.Range(Cells(drownum, dcolnum)).Value = Target.Value
End If
End Sub
I'm still new to VBA and I don't know what prompted these errors, if this is syntax or a fundamentally wrong way of going about the problem. I would appreciate any help and assistance. Thank you!
18498