Check box help with user made function

[WillShakespeare]

Weird, I trie dto post the below message, but it seems not to appear. I have also posted it in Office area in case someone there can help. Happy to delete one or other once I find out which is more popular...

In a spreadsheet I have a checkbox that when selected I want a cell to appear (let's call it "new") and a checkbox next to it.

I can use CHOOSE() function to put the name "new" into the cell, based on the existingc checkbox, but I struggled to get the "new" checkbox to appear.

So, I delved into creating a function and here is what I have:

Function InsertNew(myCheck As Boolean)
    If myCheck = True Then
        ActiveSheet.Shapes("Check Box 37").Visible = True
        Range("A19").Select
        ActiveCell.FormulaR1C1 = "New"
        InsertNew = True
    Else
        ActiveSheet.Shapes("Check Box 37").Visible = False
        Range("A19").Select
        ActiveCell.FormulaR1C1 = ""
        InsertNew = False
    End If
End Function

Oddly, some of it works, but some of it doesn't! The "new" checkbox becomes visible or invisible as required, but "New" does not appear in cell A19 and I get a #VALUE error for the return value True or False, as required.

Can anyone help?

Thanks in advance,

Will

 

[WillShakespeare]

Ah I have made a bit of progress. If I rem out the Range and ActiveSheet.FormulaR1C1 part (the middle 2 lines of my IF statement and Else), then it all works. So I guess all I need is to know how to insert text into cell A19 from a function?

Can you help?

Will

 

[PHV]

Replace this:
Range("A19").Select
ActiveCell.FormulaR1C1 = "New"
with this:
Range("A19").Value = "New"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WillShakespeare]

Tried that, but thanks.

I think the issue is that you cannot affect the Excel environment directly from functions, even if you call marcos or sub routines.

In the end I created a label next to the checkbox and managed to make these appear and disappear.

Again, thanks.

Will

 

[PHV]

I confirm that an UDF can't change directly a Cell's vae.lu

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fumei]

Why is it even a Function, rather than a Sub? I see no need for it to even be a UDF. And a Sub with

ActiveSheet.Range("A19").Value = "New"

certainly does put "New" into the cell.

Gerry

 

[WillShakespeare]

Mainly because I tried doing it outside of a function, but couldn't figure out how. I want a cell to appear when a checkbox is ticked. What I did was use a label instead of a cell, linked to the checkbox. I then checke dthe linked-cell to the checkbox using an IF statement where the test was my UDF: InsertPly(Q5)

The UDF returns True or False and also makes the new label and checkbox hide (false) or unhide (true).

Function InsertPly(myCheck As Boolean)
    If myCheck = True Then
        ActiveSheet.Shapes("cbPlywood").Visible = True
        ActiveSheet.Shapes("lbPlywood").Visible = True
        InsertPly = True
    Else
        ActiveSheet.Shapes("cbPlywood").Visible = False
        ActiveSheet.Shapes("lbPlywood").Visible = False
        InsertPly = False
    End If
End Function

cb = Checkbox
lb = Label

It works nicely, though I would still like to know how I could have done this witha cell rather than a label.

Will

 

[WillShakespeare]

Here's the cell value that calls it:


=IF(InsertPly(Q5),(VLOOKUP('TSD-Quote'!A18,'Extras Available'!A29,2,FALSE)),(VLOOKUP('TSD-Quote'!A18,'Extras Available'!A29,3,FALSE)))


The reason for the multiple lookups is different values based on whether they have cost per sq metre or per metre (some products are one or either and tehse need to be updated by simply changing the values).

Will

 

[fumei]

Technically, your Function

Function InsertPly(myCheck As Boolean)

returns a Variant, NOT a Boolean. No data type is declared.

Function InsertPly(myCheck As Boolean) As Boolean

declares the data type of the Function.

My point being is I wonder what is the point of InsertNew = X?

Function InsertNew(myCheck As Boolean)
    If myCheck = True Then
        ActiveSheet.Shapes("Check Box 37").Visible = True
        Range("A19").Select
        ActiveCell.FormulaR1C1 = "New"
        InsertNew = True
    Else
        ActiveSheet.Shapes("Check Box 37").Visible = False
        Range("A19").Select
        ActiveCell.FormulaR1C1 = ""
        InsertNew = False
    End If
End Function

If it worked - tested the value of myCheck, and actioned the .Visible, and put the string "New" into A19 - why do you need InsertNew?

[b]Sub[/b] InsertNew(myCheck As Boolean)
    If myCheck = True Then
        ActiveSheet.Shapes("Check Box 37").Visible = True
        ActiveSheet.Range("A19").Value = "New"        
    Else
        ActiveSheet.Shapes("Check Box 37").Visible = False
        ActiveSheet.Range("A19").Value = ""
    End If
End Sub


' in the calling sub
Call InsertNew(ActiveSheet.Shapes("Check Box 37").Value)

Gerry