Hide/Show cells based on other values

[sadicu]

Hi guyz,


I need help with the following :


I am creating an excel template and I want to develope the excel customer for each country. For example if the user select coutry GB i would like some cells to be visible such as (A1, A2, A3). If the country selected is ES then I would like to have cells A1,A4,A5 visible and A2 & A3 hide.


Does it makes any sense? Thanks in advance.

 

[kaushik03]

Hi sadicu,


Firstly welcome to chandoo.org...we glad to have you here


Assuming in G1 of sheet2 you have the text GB/ES. The code would be:


Sub H/UH()


If Sheet2.Range("G1").Value = "GB" Then


Range("A1:A3").EntireRow.Hidden = False


Else


Range("A2:A3").EntireRow.Hidden = True


End If


End Sub


Kaushik

 

[sadicu]

HI, Many thanks for your reply and welcome words.


However, as you can imagine for noobs its not working


So G1 is a drop-down list with 3 values (blank,GB,ES).


Cells A1 ="1",A2="2"...A5="5". Whenever G1 = GB cells A1,A2 & A3 to remain visible and A4 &A5 to be hiddedn.

When G1 is = to ES A1,A2,A3 to be hiden and only A4 A5 to remain visible.


To be more specific i-am trying to play with the banking info necessary depending on country.

 

[kaushik03]

Hi sadicu,


Can you plz check this file and see if it is fine?


http://speedy.sh/DTGky/Hide-and-unhide-macro.xlsm


Kaushik

 

[sadicu]

Hi Kaushik03, thats what I need

 

[kaushik03]

Great....glad to help you..


Kaushik

 

[sadicu]

Can it be done with an auto-refresh?? and remove the HIT ME button ?

 

[kaushik03]

Hi sadicu,


Need to run the code under Worksheet_Change event. Write click on sheet1, hit "view code" to see the code.


http://speedy.sh/nJSKr/Hide-and-unhide-macro.xlsm


However, the code is here:


Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Target.Worksheet.Range("$G$10")) Is Nothing Then

Call MyMacro

End If

End Sub


PS: Just change the option from drop down, macro will automatically trigger to execute the desired job.


Kaushik

 

[sadicu]

Perfect, works great. One more question if I want to apply this to certain cells what do I use instead of Range? If I want to display just A3 and A5 ?

 

[kaushik03]

Hi sadicu,


Glad that it worked..


Do you mean to say, only A3 and A5 should be visible and all other rows in excel should be hidden? If yes, then what would be the condition to do so?


Probably I did not get you clearly. Plz explain....


Kaushik

 

[sadicu]

Hi Kaushik03,


Yes i mean instead of range I would like to have visible just A1 & A3 rest to be hidded, When another value is selected (ES for example) to have A2 & A4 visible and the rest hidded.

 

[sgmpatnaik]

@sadicu

Hi


Please download and try the below file i changed the little bit format of Kaushik03 as per your required


https://dl.dropbox.com/u/75654703/Hide%20and%20unhide%20macro.xlsm


Thanks


SP