Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Hide/Show range based
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Hide/Show range based
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





Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Hide/Show range based on Command Button
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

"AMell" wrote:

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





Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,501
Default Hide/Show range based on Command Button
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

"AMell" wrote:

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

"AMell" wrote:

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





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Hide/Show range based on Command Button
Superb Mike, thank you very much for your help.

Anne



"Mike H" wrote:

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

"AMell" wrote:

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

"AMell" wrote:

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





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Hide & Show Rows based on Check Boxes loza Excel Discussion (Misc queries) 1 September 14th 08 01:49 AM
hide / show rows based on two drop down selectiosn Maritza Excel Programming 3 April 1st 07 10:26 AM
Hide/Show a row based on data entry in another row ivory_kitten Excel Programming 7 May 17th 06 12:08 PM
Macro for Show/Hide Range Bill Excel Discussion (Misc queries) 3 December 1st 04 05:33 PM
Macro to Hide/Show Columns based on control cell value Steve N Excel Programming 2 May 25th 04 06:51 PM


All times are GMT +1. The time now is 05:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement