Can I hide cells that contain a Vlookup Function but have the function still work?-CodePudding

I am working on an excel project and need some help. The idea of the project is for a user to type in a product id number, and have the corresponding image appear on a different cell. In order to make this work, there is an other cell which runs a Vlookup and checks for that id number on a product database, stored in a different sheet. I then use VBA to output the image onto the correct cell.

I'm just wondering if it's possible to hide the cells with the Vlookup function but still have it run when a sure changes the cell with the ID number? Any help is appreciated. Thank you.

CodePudding user response：

I would use a function like the following one

Function getval() As Variant
    Dim ws As Worksheet
    Dim rg As Range
    
    Set ws = Worksheets("Input") ' sheet used for input
    Set rg = ws.Range("F10")  ' cell with the productid
    
    Dim wsData As Worksheet
    Set wsData = Worksheets("Data")  ' sheet with the data
    
    Dim rgData As Range
    Set rgData = wsData.Range("A1:B4")  ' range with the data
    
    getval = WorksheetFunction.VLookup(rg, rgData, 2, False)  ' replace the 2 with the column you need
End Function

You can test it

Sub testit()
    Debug.Print getval
End Sub

CodePudding user response：

This one is very simple using VBA (see https://techcommunity.microsoft.com/t5/excel/lock-cell-without-protecting-worksheet/m-p/2360200). You open the VBA editor and select the Worksheet you want to protect. Add this code inside the same sheet, and this will automatically "correct" the formulas if someone decides to change them.

' protect cells in worksheet, change back if they get modified
' for individual cells separate them by commas like (Range("A1, B1, C1")
' or use ranges

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("H12:O14"), Target) Is Nothing Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
    End If
End Sub

Note that macros need to be enabled for this to work. I added it to a spreadsheet I need to protect cell ranges on and it works as expected. If you type anything other than what exists in the cell already, it automatically changes it back.