Facing an issue in using macros in excel sheet.

Tanu Bhardwaj 0

I am using the coding method to hide/unhide the columns using the data validation list. whereas its working fine with one selection perfectly whereas when i change to other option from the data validation list, its not hide/unhiding the required data, Please help me, I think it would be better if you contact me 1 to1 by showing my concern to you.

Thanks

Barry Schwarz 3,076 Reputation points

2024-11-29T11:06:45.6233333+00:00

You could start by showing us the macro.
Tanu Bhardwaj 0 Reputation points

2024-11-29T11:29:31.7966667+00:00

How can i do that?
Tanu Bhardwaj 0 Reputation points

2024-11-29T15:33:35.3766667+00:00

Please let me know urgently, as i need to sort it asap.
Barry Schwarz 3,076 Reputation points

2024-11-29T16:27:56.2733333+00:00

Copy and paste the macro into your message. Also tell us which option works and which does not.
Tanu Bhardwaj 0 Reputation points

2024-11-29T22:36:58.09+00:00
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ProjectType As Range

Set ProjectType = Range("B6")

If Intersect(Target, ProjectType) Is Nothing Then Exit Sub

'add as many data sets as required

Dim Rng1 As Range

Dim Rng2 As Range

'add as many headings as you require

Dim FindHdg1 As Range

Dim FindHdg2 As Range

'put your headings in the brackets & add more headings if required

Set FindHdg1 = Cells.Find("LICENSING INCOME")

Set FindHdg2 = Cells.Find("COMMISSION INCOME")

Dim ColsToHide As Range

Set ColsToHide = Range("D1:XFD1")

'add a case for each option in your drop-down & and add more if required

Select Case ProjectType Case Is = "All" Cells.EntireColumn.Hidden = False Case Is = "Licensing" Cells.EntireColumn.Hidden = False Set Rng1 = FindHdg1.CurrentRegion ColsToHide.EntireColumn.Hidden = True Rng1.EntireColumn.Hidden = False Case Is = "Commission" Cells.EntireColumn.Hidden = False Set Rng2 = FindHdg2.CurrentRegion ColsToHide.EntireColumn.Hidden = True Rng2.EntireColumn.Hidden = False End Select

End Sub
Tanu Bhardwaj 0 Reputation points

2024-11-29T22:39:54.7433333+00:00

I am using the above VBA In View code.

When I am choosing the licensing option in Data validation, It works fine.

Then, when I change the option drop down list of data validation to commission it doesn't work properly in hiding the rows/columns as per the requirement
Tanu Bhardwaj 0 Reputation points

2024-11-29T22:43:06.6733333+00:00

I am trying to post the code whereas it has been deleted as it says voilation of Code of conduct

Please suggest me other way so that i can share my concern
Tanu Bhardwaj 0 Reputation points

2024-12-01T19:30:50.36+00:00

Hi, Can you please help?
Viorel 118.5K Reputation points

2024-12-01T20:19:17.7233333+00:00

Does it work if you replace Select Case ProjectType with Select Case Trim(ProjectType.Value)?

Make sure that the worksheet contains all of the strings that appears in VBA code. Actually you can use the Debugger to put breakpoints, check the values of variables, execute the code step-by-step.
Tanu Bhardwaj 0 Reputation points

2024-12-01T21:59:26.85+00:00

Working, thanks

Share via

Facing an issue in using macros in excel sheet.

Your answer