Why does this happen?

  • Thread starter Thread starter timbob
  • Start date Start date
T

timbob

Weird one this.....

I've got a spreadsheet with many tabs thats used as a
costing tool. On my input tab, I have a drop down list to
state what sort of costing it is; just A, just B or A&B.
I got this great bit of code that hides rows repending on
what the answer is:

Private Sub Worksheet_Change(ByVal Target As Range)

Rows("13:37").EntireRow.Hidden = False

If Range("c5") = "A" Then
Rows("13:18").EntireRow.Hidden = True
ElseIf Range("c5") = "B" Then
Rows("20:37").EntireRow.Hidden = True
End If

End Sub

This is a private sub on that worksheet.

Problem - on another sheet I'm summarizing information
that is on other tabs in the spreadsheet. This sheet
references a module that I got from VBnet - to convert
numbers (1,2,3) into text (one, two, three).
On the summary sheet, if the cell with the summed up
number in uses a worksheet formula, such as ROUNDUP or
SUM, the bit of code above stops working. This is really
odd. As soon as I remove the functions and just type the
number in, it works again.

Any ideas?
 
Just a guess, but maybe there's an error in the function that converts numbers
to text. (I'm guessing that the code may look for .value. If you have a #n/a
in that cell, it'll blow up pretty good.)

But you may be able to make your code a little more stringent. If C5 changes by
typing:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("c5")) Is Nothing Then Exit Sub

Me.Rows("13:37").EntireRow.Hidden = False

Select Case UCase(Target.Value)
Case Is = "A": Me.Rows("13:18").Hidden = True
Case Is = "B": Me.Rows("20:37").Hidden = True
End Select

End Sub

I looked at A and a as the same (UCase). And sometimes select case makes things
a little clearer than if/then/elseif.
 
Back
Top