Hide/Show range based

[Guest]

Hi All,

Hoping someone can help (again).

I have a command button that when clicked runs a macro to confirm the
validity of certain selections (Select Case Else). That all works fine.

What I would like to do now is to hide a range (A14:T25) if the final case
in the code is true. The range should stay visible in all other instances.


Hope this makes sense and someone can advise. Code is below:

Sub ConfirmSelection()
Dim Msg As String
Select Case Range("AB17").Value = "5.4" And _
Range("AB16").Value >= 4 And Range("AB16").Value <= 7.1
Case True
Msg = "OK"
'Range should be visible
Case Else
Select Case Range("AB17").Value = "6.8" And _
Range("AB16").Value >= 4 And Range("AB16").Value <= 8.8
Case True
Msg = "OK"
'Range should be visible
Case Else
Select Case Range("AB17").Value = "8" And _
Range("AB16").Value >= 7.8 And Range("AB16").Value <= 14.3
Case True
Msg = "OK"
'Range should be visible
Case Else
Msg = "Not OK, Please Re-select Within Given Parameters"
'Range should be hidden
End Select
End Select
End Select
MsgBox "Indoor/Outdoor Unit Ratios " & "" & Msg
End Sub

 

[Guest]

Edit:

On second thoughts, it would be better if the range was hidden by default
and only made visible when the command button was clicked and the results
determined to be 'OK'.

Can this be done?

Thanks

 

[Guest]

Hi,

Hiding rows or colums is simple enough but hiding a block of cells in the
middle of a sheet is (I believe) not dooable. However you could hide the
contents of those cells until suck time as you get the correct response from
select case but it involves formats and if anyone selects the cells then they
would see the values in the formula bar. Anyway it may be of some use so:-

Sub ConfirmSelection()
Range("A14:T25").Select
Selection.NumberFormat = ";;;"

Dim Msg As String
Select Case Range("AB17").Value = "5.4" And _
Range("AB16").Value >= 4 And Range("AB16").Value <= 7.1
Case True
Msg = "OK"
Range("A14:F21").Select
Selection.NumberFormat = "General" 'Range should be
visible
Case Else
Select Case Range("AB17").Value = "6.8" And _
Range("AB16").Value >= 4 And Range("AB16").Value <= 8.8
Case True
Msg = "OK"
Range("A14:F21").Select
Selection.NumberFormat = "General" 'Range should be
visible
Case Else
Select Case Range("AB17").Value = "8" And _
Range("AB16").Value >= 7.8 And Range("AB16").Value <= 14.3
Case True
Msg = "OK"
Range("A14:F21").Select
Selection.NumberFormat = "General" 'Range should be
visible
Case Else
Msg = "Not OK, Please Re-select Within Given Parameters"
'Range should be hidden
End Select
End Select
End Select
MsgBox "Indoor/Outdoor Unit Ratios " & "" & Msg
End Sub

Mike

 

[Guest]

Superb Mike, thank you very much for your help.

Anne