Excel VBA ClearContents of validation cell
This problem has been plaguing me for the last day or so. I am developing an addon to Excel to make a few new commands that help me do some tasks in a worksheet I'm making for work. My particular problem is that I am trying to make a command that will look at a cell and if it's value is "No" then it needs to clear the contents of a couple of data validation cells to make the sheet easier to read. However, I'm having a hard time getting this to work. I have a function which will look at a couple of different options like "yes" or "no" what have you. It then calls a subroutine that will clear the boxes for me as I have found out functions cannot clear cells.
Here is the Function:
Function ClearDropList(check) 'If check is no, execute the code below If check = "No" Then 'Clear the desired Cells Call clearTankList 'Make our actual "ClearDopList" box invisible to the user ClearDropList = "" End If 'If check is yes, make our "ClearDropList" box invisible, but change nothing If check = "Yes" Then ClearDropList = "" End If End Function
And the subroutine I have
Sub clearTankList() Worksheets("sheet1").Range("D7").ClearContents End Sub
It can only work if you call the function from within a sub rather than a cell. Try running MAIN:
Sub MAIN() MsgBox ClearDropList("Yes") MsgBox Worksheets("Sheet1").Range("D7").Value MsgBox ClearDropList("No") MsgBox Worksheets("Sheet1").Range("D7").Value End Sub Function ClearDropList(check) If check = "No" Then Call clearTankList ClearDropList = "" End If If check = "Yes" Then ClearDropList = "" End If End Function Sub clearTankList() Worksheets("Sheet1").Range("D7").ClearContents End Sub
If you're trying to create a user-defined function that works out of a cell, like
then it won't work, because UDFs are not allowed to change the value of any other cell. It won't work even if the work is done in another subroutine, if it's called by the UDF.