Posts by ignasis

Re: Deselect filter in slicers with VBA

Well, I managed to solve my problem just adding

.clearManualFilter

So the final code:

Private Sub CommandButton1_Click()


With ActiveWorkbook.SlicerCaches("SegmentaciónDeDatos_MES")
[B]    .ClearManualFilter[/B]




  For i = 0 To ListBox1.ListCount - 1
   If ListBox1.Selected(i) = True Then
        .SlicerItems(i + 1).Selected = True
    ElseIf ListBox1.Selected(i) = False Then
        .SlicerItems(i + 1).Selected = False
    End If
  Next i




End With




  Set CurrentChart = Sheets("Hoja2").ChartObjects(1).Chart
    Fname = ThisWorkbook.Path & "\temp.gif"
    CurrentChart.Export Filename:=Fname, FilterName:="GIF"
   Image1.Picture = LoadPicture(Fname)




End Sub

Quite simple!

Hello!

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:

Private Sub CommandButton1_Click()


With ActiveWorkbook.SlicerCaches("Slicer_Month")




  For i = 0 To ListBox1.ListCount - 1
   If ListBox1.Selected(i) = True Then
        .SlicerItems(i + 1).Selected = True
    ElseIf ListBox1.Selected(i) = False Then
        .SlicerItems(i + 1).Selected = False
    End If
  Next i




End With




  Set CurrentChart = Sheets("Sheet2").ChartObjects(1).Chart
    Fname = ThisWorkbook.Path & "\temp.gif"
    CurrentChart.Export Filename:=Fname, FilterName:="GIF"
   Image1.Picture = LoadPicture(Fname)




End Sub

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:

Option Explicit


Sub SelectMultipleSlicerItems()




    Dim oSlicerItem As SlicerItem
    Dim vItems As Variant
    Dim vMatchVal As Variant
    
    Application.ScreenUpdating = False
    
    vItems = Array("SER_Ft Myers_2015", "SER_Gainesville_2015", "SER_Miami_2015")
    
    With ActiveWorkbook.SlicerCaches("Slicer_Plan")
        .ClearManualFilter
        For Each oSlicerItem In .SlicerItems
            vMatchVal = Application.Match(oSlicerItem.Name, vItems, 0)
            If IsError(vMatchVal) Then
                oSlicerItem.Selected = False
            End If
        Next oSlicerItem
    End With
    
    Application.ScreenUpdating = True
    
End Sub

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,

Thanks!

Re: Populate listbox with filtered range

Ahá I got it, cool! Thanks very much for all your help and suppor jaslake!

Have a nice day!

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:

Private Sub CommandButton6_Click()


Dim rng As Range
Dim LR As Long




LR = Cells(Rows.Count, "A").End(xlUp).Row




Set rng = .Range("A1:A" & LR).SpecialCells(xlCellTypeVisible)
With ListBox1
    .ColumnCount = 7
    For Each Cel1 In rng
        .AddItem CStr(Cel1.Value)
        .List(.ListCount - 1, 1) = Cel1.Offset(0, 1).Value
        .List(.ListCount - 1, 2) = Cel1.Offset(0, 2).Value
        .List(.ListCount - 1, 3) = Cel1.Offset(0, 3).Value
        .List(.ListCount - 1, 4) = Cel1.Offset(0, 4).Value
        .List(.ListCount - 1, 5) = Cel1.Offset(0, 5).Value
        .List(.ListCount - 1, 6) = Cel1.Offset(0, 6).Value
        .List(.ListCount - 1, 7) = Cel1.Offset(0, 7).Value




    Next Cel1
End With




End Sub

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
next

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

Re: Populate listbox with filtered range

jaslake, thanks for your time!

unfortunately I can't test your code until tomorrow, I will edit and comment as soon as possible.

thanks!!

Hello!

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:

Private Sub CommandButton4_Click()


    Dim sCriteria As String, rngList As Range, sRngAddress As String
     
    sCriteria = Me.cboItem2.Value
     
    With ActiveSheet
        .AutoFilterMode = False
        .Range("A1:H" & .Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter field:=5, Criteria1:=sCriteria
         Set rngList = .Range("A2:H" & .Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
 '       Sheet3.Cells.ClearContents
 '       rngList.Copy Sheet3.Range("A2")
 '       sRngAddress = "SHeet3!" & .Range("A2").SpecialCells(xlCellTypeVisible)
        
    End With
    
'    With Me.ListBox1
'        .RowSource = sRngAddress
'    End With


'    Set rngList = Nothing


    Me.ListBox1.List = rngList.Value

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?

thanks!!

Re: Chart depending on combobox selection

any idea someone?

Any help will be so much appreciated.

Thanks in advance.

Hello,

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.

forum.ozgrid.com/index.php?attachment/68947/

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:

Dim ws As WorksheetDim lcol As Long


Set ws = Sheets("Home")


With ws
    For lcol = 3 To .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, searchdirection:=xlPrevious).Column
        If CStr(.Cells(5, lcol)) = cboYear.Value And .Cells(6, lcol) = cboMonth.Value Then
        
'now this should select the range depending on combobox selection


        End If
    Next


End With

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 http://www.excelguru.ca/forums…-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

sure it helped, I did understand everything!

thank you very much sir!

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!

*EDIT:

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
Jump:
TextBox1.Value = vbNullString

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

thanks again sir!

Hello!

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
forum.ozgrid.com/index.php?attachment/68735/