Posts by ignasis

    Re: Deselect filter in slicers with VBA

    Well, I managed to solve my problem just adding


    So the final code:

    Quite simple!


    I am trying to control the slicers of a pivot chart with Visual Basic. What I've done is to create a Listbox with the 12 months (in Excel, the slicer I have is for the 12 months too).

    The problem I am having: when I start the userform, in Excel slicer there is always one option selected. So for example I choose in userform the last 3 months and it will show also January. (In excel, there is no way to deselect all)

    This is the code I am using to select from Listbox and show in my userform chart:

    This is working good, except for the item which is selected by default.

    I found this code (by Dominic) which SOLVES the problem of default selection:

    This code is written for 3 items in slicers, but what I'm trying to do is to combine it with my listbox code.

    Any help would be greatly appreciated,


    Re: Populate listbox with filtered range

    ohh I see..wasn't counting the (0) first line!

    Now everything is OK!

    edit: sorry, just saw your previous post. What difference makes adding sheets("sheet1")?

    Thank you so much for all your help!!

    Re: Populate listbox with filtered range

    Again, thanks for the quick and good answers!

    I understood everything, except for the List Count. In your code, your first additem line is (.ListCount -1, """1"""). Why this 1 is not a 0?

    And just to make sure, cel1 should be declared as range?

    thanks again sir!

    Re: Populate listbox with filtered range

    hello again jaslake,

    this is what I adapted from your code:

    this is giving me Error: reference not valid in the line

    Set rng = .Range("A1:A" & LR).SpecialCells(xlCellTypeVisible)

    tried also with range("A2:H") but error too.

    EDIT: I tried removing the point (.) before Range and now it works perfect!! And why doesnt work with the point? I have another line very similar and with .range and it works (bad, but works).

    Another question...why If you have 10 columns, listcount is only 9?

    And last..why is it working without declare "cel1"?

    thank you very much jaslake!

    Re: optimize vba code

    You can use a loop, like this:

    for i=1 to x
    if label & i = "" then
    Msgbox "please select..."
    elseif Btn & i = false then
    label & ii.enabled = true
    end if

    I'm afraid my vba skills are not enough to provide you the full code, but I think this is the way to go.


    I've seen some threads about this but i can't get the code work.

    This is the best code I've found for my situation:

    but I dont want to copy and paste, that's why I comment that lines. Just filter and show in the listbox, all in same sheet.

    Actually, with the above code I see in the listbox only the first filtered row.

    EDIT: Problem of only showing 1st row might be because when you have a filter, the range is divided in area and only the first one is passed?

    I also have seen some comments about transposing the range to create a vector and pass it to the listbox?



    I would like to ask you about charts. My first problem is that I don't know which of the different methods to use charts in vba is better for my situation.

    I attach you an example which I think you will understand easy.

    What I need is to show a chart depending on combobox selection. However, this charts are not previously created, so the code should select the correct range and display a chart. And I don't need the charts saved as .gif files. An idea would be to create the .gif and when I close the form, delete that gif.

    So, for example:

    - I select May 2016 and Chairs --> it should appear a column chart with only 1 column showing number of chairs sold in May (this case would be cell G11)

    - I select May 2016 and Chairs and checkbox Year to Date --> it should appear a column chart with 5 columns, every column for number of chairs sold from January to May (cells C11:G11)

    - I select May 2016 and show all and Year to date --> it should appear a column chart displaying tables, chairs, beds and tvs sold from january to may (C8:G8, C11:G11, C14:G14 and C17:G17)

    I was going to use a code like this:

    and the chart code I found that maybe could be used for this:

    Dim Fname As String
        Call SaveChart
        Fname = ThisWorkbook.Path & "\temp1.gif"
        Me.Image1.Picture = LoadPicture(Fname)
    End Sub

    Private Sub SaveChart()
        Dim MyChart As Chart
        Dim Fname As String
        Set MyChart = Sheets("Home").ChartObjects(1).Chart
        Fname = ThisWorkbook.Path & "\temp1.gif"
        MyChart.Export Filename:=Fname, FilterName:="GIF"
    End Sub

    I hope I explained good, and thanks for your attention!

    I also post this message in another forum…-charts&p=23915#post23915. User Jon Peltier answered a great way to do it in excel sheets, but I would need to find a way to do it by vba.

    Re: data addition to excel filtered by comboboxes

    Fuzz-Head, that's exactly what I was looking for!! thanks for your time!

    Even your design gives me more things to learn and apply.

    Thank you SO much!


    if you don't mind, could you explain me what does these lines do?

    For lCol = 2 To .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column           
     GoTo Jump
    TextBox1.Value = vbNullString

    which is the difference between vbNullString and "" or 0?

    thanks again sir!


    On 1st form, user selects day, month and colour (with comboboxes). On 2nd form, user writes cost, prize, entries, received and %. Then click on Save.
    This data will go to an Excel worksheet template which has 3 tabs (red, green, blue).
    What I need is a code so when I select on 1st form "2016", "may" and"colour blue" --> data will save in the 3rd tab (blue) and in the respective cell.

    thanks very much!

    here is an example