• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Display Msgbox for the Columns filtered instead of formatting cells.

J

Jonnathanjons

Member
Hello,
I have a script which Highlights what are the columns which are filtered by changing the format of the column header. I found this is tampering my actual format of the report so I've to change the format back to its Original ever time I run this scrip. Can you please enrich the script to Display the Column number in a MsgBox instead of formatting the cells

Code: 
Option Explicit
Function DisplayFilter() As String
Dim i As Long
Dim ws As Worksheet

Set ws = ActiveSheet
If Not ws.FilterMode Then
    DisplayFilter = "No Filter"
    Exit Function
End If
For i = 1 To ws.AutoFilter.Filters.Count

    If ws.AutoFilter.Filters(i).On Then
        'ws.Cells(1, i).Interior.Color = vbRed   '<<<< Need help here to show MsgBox'
        ws.Cells(1, i).Font.Color = vbRed
        ws.Cells(1, i).Font.Bold = True
    End If
Next i

End Function

Sub ShowFilter()
Call DisplayFilter
End Sub
 
Hello, as a header is already 'marked' when filtered so no need any VBA procedure !​
Now the question is - as you can obviously see the filtered columns - why do you need such useless message box ?​
Maybe for a school homework ?​
Why don't you just replace the codelines within the If block with your expected MsgBox ?!​
According to your codeline Cells(1, i) what the variable i is relative to ? Like yet explained in VBA help…​
​
 
rlv01 said:
Instead of trying to add a MsgBox, why not just fix the code so that header cell is correctly changed as per your original intent?
Click to expand...
thanks for the response ..I think that worked only for that workbook you shared. I would prefer to use personal.xlsm file to apply to all activeworksheet. but thanks for the help.
 
Jonnathanjons said:
thanks for the response ..I think that worked only for that workbook you shared.
Click to expand...

Why do you find that surprising? It was a working example to show you one way it can be done and hopefully apply that knowledge to your own coding efforts.

Jonnathanjons said:
I would prefer to use personal.xlsm file to apply to all activeworksheet. but thanks for the help.
Click to expand...

That won't be possible if you want it to run automatically when you change the filter. For that to happen there MUST be event code the workbook that contains the worksheet you want to filter. Code in you personal macro workbook will not be enough.

If you just want to run the code manually, like with a button, you can move the modified displayfilter function in my example (Function DisplayFilter() As String directly into your personal macro workbook and it should work.
 
rlv01 said:
Why do you find that surprising? It was a working example to show you one way it can be done and hopefully apply that knowledge to your own coding efforts.

That won't be possible if you want it to run automatically when you change the filter. For that to happen there MUST be event code the workbook that contains the worksheet you want to filter. Code in you personal macro workbook will not be enough.

If you just want to run the code manually, like with a button, you can move the modified displayfilter function in my example (Function DisplayFilter() As String directly into your personal macro workbook and it should work.
Click to expand...
thanks for the response. When I removed the data in Sheet 2 the script did not work so am not sure I cannot run this from personal.xlsb. Also cannot have this script coded for each workbook when I receive.. But This is a great way of highlighting the filtered range if it disables the highlight when filter is removed. I sometimes get huge volume of data ..example 150 to 200 columns sometimes filtered . All I wanted was a Msgbox which can display the filtered range. ..I found a solution as a gentleman helped me tweaked the original script to below . it works. thanks again.
Code: 
If ws.AutoFilter.Filters(i).On Then
            DisplayFilter = DisplayFilter & ", " & _
                Split(Cells(1, c + i - 1).Address, "$")(1)
        End If
 
Jonnathanjons
Please reread and follow Forum Rules, those are for You too:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
Marc L said:
As I have no idea Truth could be 'sarcarstic' …​
​
Click to expand...
Marc L . Truth? Who is speaking about Truth or lies in this forum? not sure what that mean. Yes since you responded I hope you understood. Your comments sometimes are ridiculous and not professional .I dont care who you think you are . You are expected to respond professionally in your comments with strangers . It may be that you are sharing your skills which make you think that your are in a better position to ridicule people who contact you. Its your lack of experience and wisdom cloud your judgement. You may be good at your skill but that does not give you any right to mock or ridicule people who contact you. Also Its not obligatory for you to respond on every post and make ridiculous comment if you do not have an answer to a query. You can ignore and let others respond. I wish your grow up some day and your actions are appreciated.
 
Jonnathanjons
Did You read #9 reply? ... I named one - the CrossPosting.

After that, did You follow How to get the Best Results at Chandoo.org ?

From Always , You could reread from Check your answers before posting a solution.

The ongoing success of this forum depends on your contribution and involvement either posting questions or more importantly answering them.
 
The ridiculous is you already have the solution just in front of your eyes when you created this thread​
according to a kid level reading VBA help.​
You are begging for a fish but I prefer to learn you how to fish …​
​
 
You must log in or register to reply here.
Back
Top