How to assign value to cell in excel VBA after clearing validation

0
votes

So my vba function should assign different values to a cell based on a boolean input. True part works perfectly, however I'm getting stuck with the False statement. The True part assigns a drop down list to the cell, and the False part should erase it, and set its value to text "N/A". As soon as I'm trying to change the value of the cell (or any cells actually, with range("..").value command, the code faces an error.

Is there an other way around? Thanks in advance!

Function sema_list(rng As Range, sema As Boolean)


If sema = True Then
    With rng.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="=arrangement"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End If

If sema = False Then
    rng.Validation.Delete
    rng.Value = "some text"
End If

sema_list = ""
End Function
2
vbaexcel

2 Answers

1
votes

A function can only put text in the cell in which it is called so you would have to do this using a normal sub (possibly with parameters). E.g.

Sub x()
sema_list Range("A1:A2"), False
End Sub

Sub sema_list(rng As Range, sema As Boolean)
If sema = True Then
    With rng.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="=arrangement"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End If

If sema = False Then
    rng.Validation.Delete
    rng.Value = "some text"
End If

End Sub
0
votes

I guess you have to pass some range before assigning any value. For eg:

Range("A1:A10") = "some text"

or maybe something like this:

Dim example As Range
Set example = Range("A1:A10")

example.Value = 8