Using a command button to alter a cells formula..

[basepointdesignz]

Hi,

Does anyone know how to change a cell's value/formula once a command button is pressed? What I want it to do is effectively disable the VAT formula from a cell, so the total cost can be calculated with and without VAT.

I have the following macro attached to the button:

Sub disableVAT()

Dim Target As Range  'Range of cells..

Select Case Target.Address
    Case Is = "$J$52:$K$53"  'Check state of VAT cell's formula/value..
    
        If Target.Formula = "=J50 * 0.175" Then
            Target.Value = "0"  ' Change cell's value to 0 (disabling VAT)..
        ElseIf Target.Value = "0" Or Target.Value = "" Then
            Target.Formula = "=J50 * 0.175"
        End If  ' Change cell's formula (enabling VAT)..
        
End Select

End Sub

The code comes up with a:

Object variable or With Block variable not set error...

.....and it points to the

Select Case Target.Address

line..

What could be happening?

Any ideas?

Cheers,

Paul @ basepoint designz..


basepoint designz
renegade@tiscali.co.uk

 

[Bowers74]

DO you have the TakeFocusOnClick property for your CommandButton set to False?



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

I looked a bit closer at your code and I noticed that there are a few more problems. Depending on whether you want to have/check for absolute of relative formulae, you will have to write the code differently.

Since I am not sure, after reading your code, where the formulae are referencing to I wrote the following bits of code, that you can try (but save a backup of your workbook first!

For absolute reference to J50 (which I doubt you want):

Sub FormulaVAT_Absolute()
Dim c As Range
Dim Target As Range
Set Target = Range("$J$52:$K$53")
For Each c In Target.Cells
    If c = 0 Or IsEmpty(c) Then
        c.Formula = "=$J$50*0.175"
    ElseIf c.Formula = "=$J$50*0.175" Then
        c.Value = 0
    End If
Next c
End Sub

For a relative reference to the cell two rows above the cell where the formula is written

Sub FormulaVAT_Relative()
Dim c As Range
Dim Target As Range
Set Target = Range("J52:K53")
For Each c In Target.Cells
    If c = 0 Or IsEmpty(c) Then
        c.FormulaR1C1 = "=R[-2]C*0.175"
    ElseIf c.FormulaR1C1 = "=R[-2]C*0.175" Then
        c.Value = 0
    End If
Next c
End Sub
[code]

I hope this helps!

Peace! [peace]

Mike

[COLOR=red][b]Never say Never!!!
Nothing is impossible!!![/b][/color]