PDA

View Full Version : EXCEL VBA - PUSH DATA AND REFRESH



cycheah
11-25-2015, 04:55 PM
Hi all,
Can anyone help me with the excel vba / macro please...

I have a cell W9 which will perform vlookup from other workbook by referring to the input at cell G8.

Cell W9 having the formula as following: =IF(ISBLANK(G8),"",IFERROR(VLOOKUP(G8,'C:\ server\engineering\drawings master list\[dwg master list current 2015.xls]CURRENT'!$D$5:$E$20000,2,FALSE),""))

Please be informed that cell W9 is actually a hidden cell (I colored the fonts to white, and lock it), user will not able to see it.
I need to do the following condition at cell W9 & X9.
1. Push and refresh the all data (inclusive blank) from cell W9 to cell X9 automatically.
2. If W9 is blank, then allow user to manually enter data at cell X9.
3. Data manually enter by user at cell X9 shall be refreshed to follow step (1) if G8 is refreshed.
4. Data manually enter by user at cell X9 shall be refreshed to follow step (1) if data at G8 is deleted or become blank.

Anyone here can help?
Deeply appreciate your input.

Thanks.

Regards,

James.

SamT
11-25-2015, 10:17 PM
Put this code in the affected Worksheets Code Page
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G8")) Is Nothing Then Exit Sub

Application.Calculate
With Range("X9")
.Locked = False
.Formula = "=W9"
.Locked = Range("W9") <> ""
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$X$9" Then Target.Locked = Range("W9") <> ""
End Sub