VBA_HELP_02/17/2015

[Ravindra]

Hi All,

I have a workbook with the record from January to August. I have added 8 button for Jan...August and two extra button for Show All and Hide All respectively.
I also added a Active X combo box for the selection of month.

Now, I want such sort of functionality with added controls so that they can show the record as per selected month.

For example:- If I click on May button then I can view May record and rest of records should be hide. Same I want to do through Combo Box, and moreover, ---Select Month--- should not react any thing.

For better convenience, I have enclosed the excel file here. Please see it, and help me in this concern.

Thanks....
Ravindra Bisht

 

[Deepak]

This post might be raised in VBA section.

 

[Nebu]

Hi:
Find the attached.

Thanks

 

[Smallman]

Hi

It is a good idea to post your solved VBA on the forum. It is kind and helps the people visiting this site who are not members and can not open the file.

I don't think the buttons are needed. A quick change to the structure of the headings and you are away.

Option Explicit
Option Compare Text
Sub HideIt()
Dim rng As Range
Application.ScreenUpdating = False
Columns("a:x").EntireColumn.Hidden = False
    For Each rng In Range("A1:X1")
        If rng <> [Y1].Value Then rng.EntireColumn.Hidden = Not (rng.EntireColumn.Hidden)
    Next
Application.ScreenUpdating = True
End Sub

To Hide all Choose (Select a month). File attached to prove workings.

Take care

Smallman

 

[Ravindra]

Please clarify me following codes. I will really appreciate you for this kind of help.

Code (vb):

Option Explicit
Option Compare Text
Sub HideIt()
Dim rng As Range
Application.ScreenUpdating = False
Columns("a:x").EntireColumn.Hidden = False
    For Each rng In Range("A1:X1")
        If rng <> [Y1].Value Then rng.EntireColumn.Hidden = Not (rng.EntireColumn.Hidden)
    Next
Application.ScreenUpdating = True
End Sub

 

[Smallman]

Hi

The code is saying any cell in Row 1 which does not contain the same value as that which exists in Y1 then hide.

The method is outlined in more detail here with a file to show workings.

Hide Cols

Hope that helps.

Take care

Smallman

 

[Ravindra]

I am not able to understand this below given code:-
For Each rng In Range("A1:X1")
If rng <> [Y1].Value Then rng.EntireColumn.Hidden = Not (rng.EntireColumn.Hidden)
Next
Application.ScreenUpdating = True
End Sub
......

As far as by this programming..I can say that you are using for each loop which is collecting value from collection("A1:X1") into Range type object (rng)...
and If rng <> [Y1].Value (if rng is not equal to selcted value from combo box0
but I am not getting at all meaning of rng.EntireColumn.Hidden = (rng.EntireColumn.Hidden)
and Application.ScreenUpdating = True


please help me............
many-many thanks for your kind support...god bless you

 

[Smallman]

Hi Ravindra

It is a false positive. So Not and False are like saying (minus minus)

or

--

which is the same as True.

As such the coding can be shortened and I think you will grasp this more easily;

If rng <> [Y1].Value Then rng.EntireColumn.Hidden = True

Try it out - does the same thing.

Also a bit easier to read.

Take care

Smallman

 

[Ravindra]

Hi Sir,

I have done little bit changes in designing . Please help me now in this concern. Moreover, your coding is really awesome. Since a coding is called perfect coding which is a little. Please......help.....
Regards,
Ravindra Bisht

 

[Smallman]

Hi Ravindra

Should do everything you wish with the dropdown alone.

Take care

Smallman

 

[Ravindra]

Hi Ravindra

Should do everything you wish with the dropdown alone.

Take care

Smallman

It is a nice solution which you have provided me, but I want cell a1:c1 to be merged in single cell a1 and so on with another cell to represent month. please see attached file with required modification....and do more needful.

Have a great day !!!!

 

[Smallman]

No I am sorry I don't agree with Merged cells. They are a nuisance IMO and should be avoided at all costs. Centre Across Selection produces the same look without the loss of fidelity.

Take care

Smallman