MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

FormulaHidden property for a merged cell in VBA

  Asked By: Fenna    Date: Sep 06    Category: MS Office    Views: 2958

I am trying to hide the formula in a merged cell on deleting some
content of the cell by using

Target.FormulaHidden = True , this works fine if i delete
the contents using Delete Key.

But If i delete using BackSpace then it shows a run time error message
"Unable to set the FormulaHidden property of the Range class in Excel
VBA Runtime error 1004" .



5 Answers Found

Answer #1    Answered By: Kim Cruz     Answered On: Sep 06

The problem seems to arise from other parts of your code. Could you post the
whole sub, please so we can see what it is doing?

Answer #2    Answered By: Adelbert Fischer     Answered On: Sep 06

I was working with this issue and found tht if i Change

Target.FormulaHidden = True TO

Range(Target.MergeArea.Address).FormulaHidden = True ,

works fine  if i use backspace  to delete  the contents. but this vl
show error  msg if i use Delete key  to delete the cell  contents.

Here is the code,

Private Sub Changed(ByVal Target As Range, Optional what As String)

Dim rowOffset As Integer
If IsEmpty(Cells(Target.Row, Target.Column)) Then

If (what <> "") Then
Select Case what
Case "Add"
Target.Formula = "=A+B"
rowOffset = 22
Case "Sub"
Target.Formula = "=C-D"
rowOffset = 23
End Select
If ((Target.Row - 1) Mod (Max + Max + 1) < AddAcc + 2) Then
Target.Formula = "=A+B"
Target.Formula = "=C-D"
End If
rowOffset = 0
End If

Target.Font.Italic = True
Target.Font.Bold = False

Target.FormulaHidden = True 'this line shows  the error.

Target.Offset(-1, 0).Select


'Selecting next cell down
Target.Offset(1, 0).Select

End If

End Sub

Answer #3    Answered By: Bian Nguyen     Answered On: Sep 06

I cannot identify the problem. I would suggest you set  a
breakpoint on the instruction and run  the macro. When it stops, go into debug
and look at what the target is, what its contents  are, etc.

I am assuming protection is turned off for the worksheet at this point. If it
is turned on, you may need to turn it off before the instruction and on again

Answer #4    Answered By: Daniel Jones     Answered On: Sep 06

I added breakpoint and checked it out. When i use DEL key  to delete
the contents  of the cell, the value of Target.Address will be
Same time  if i use backspace  to delete  the contents, the value of
Target.Address is $C$168. and i think this is where the problem is.

I am still not able to find the solution and working with it.

Answer #5    Answered By: Mercedes Andrews     Answered On: Sep 06

Untested, but try ...


This should give you $C$168 no matter what came in.

Didn't find what you were looking for? Find more on FormulaHidden property for a merged cell in VBA Or get search suggestion and latest updates.