Posts by Max0d

Hi,

I have 4 pivot table filters and I have set up the rest of a dashboard using the cube formulas. This is all working great. Finally, when the user changes a pivot field filter, the CUBERANKEDMEMBERS are ordered on a tuple such that sometimes the values are blank.

I have a macro to hide/show these blank rows - but what I want to do is call the hide/show macro when a filter changes and the new values has been retrieved from the cube. I have tried using various combination of Worksheet_Change and Worksheet_Calculate but the results are proving to be reliable. The result will then be almost like a pivot table but much more flexible. Any help is much appreciates.

Thanks an advance for your time - I am using excel 2010 for this BTW.

Using Excel 2010 I have created a pivot table and I am matching the items to be displayed with code that refers to .PivotItems(x). I have a list of raw data that has 13 entries, and these show up in the pivot catagory filter. However, with the VBA code I can see that the count of pivot catagory items is 14. When I output the full list I noticed that there are 2 enties for the same line of data in the format:

Region X &amp Y (item 13)
Region X & Y (item 14)

Now the selected item is item 14, but because item 13 does not exist as a selection(I have checked and recoded that raw data to remove the ampersans), the value of x never iterated to 14 which is the item I want to show/hide.

Cliff notes:

In summary using code in the format:

thePT.PivotFields(PivotCatFiler).PivotItems.Count

Resolves to 14 when there are only 13 possible selection in the raw data and the pivot table catagory filter.

Has anyone ever seen this?

Re: Active X controls changing size & front when opened in 2007 created in 2010 64 bi

Ok - so blatant bump...but I've just opened this workbook in excel 2007 but have not enabled macros. This file has loaded and it looks exactly as it should. So what is it that could be changing the font inside the listboxes, the listboxes themselves and the fonts on the command buttons?

Hi,

I was wondering if anyone else has encountered this problem. Basically, I have created a rather involved workbook in 2010. This has a number of Listboxes (activex) that drive user selections etc. I have done this in Excel 2010 64bit in Windows 7. However, my client will be using Excel 2007. When I open the same sheet in Excel 2007 in a windows XP 32 bit environment the list boxes change size and the font are different in the buttons and listboxes.

I do have a lot of code that fires on the workbook open event (basically a reset routine which resets filters, pivot tables and listboxes). The resizing occurs once this has fired.

I had previously read something about setting the IntHeight property to false would prevent this (this is off the top of my head) and that would fix this. But this has not seemed to help.

I have implemented a temporary work around which was to save the workbook from 2010 in 2003 format. Open this in 2007 and then save as macro enable workbook. The listboxes didnt change size but the fonts did. Also, the code seems to take a lot longer to run. And as such I am not entirely happy with this solution.

If anyone can offer any insight into this I would be eternally gratefull as this project has taken up so much of my life recently!

Thanks in advance for the taking the time.

Re: Move Down One Cell In An Active Filter

Exactly what I was looking for. Thanks a lot : D

Is there anyway to move down just one cell using VBA if an active filter is on, i.e. the next row down from row 10 may be row 200, but I just want to navigate the cells that are active. Whenever, I try to record a macro and have a look excel gives me an absolute cell reference. Thanks in advance.

Hi, I know there is a way to do this but I have forgotten. Suppose I have a list of objects in column A (row 2 to 10) and accross row 1 (columns B-E) I have shop names. The corresponding matrix (B2:E10) is the price of each item in each shop. If I return the maximum value of the row for item in cell F2, in cell G2 I now want to lookup the shop name that value corresponds to, i.e. the max of item 1's value is in cell D2, I want to return the value in cell D1 (the shop number) and so on. Hope I have explained this well enough. Many thanks.

Re: Find Max Value In A List Based On Vlookup Criteria

Find a simple example attached. Basically I want to find a way of returning the maximum revenue each week. If someone can help me with this I'm sure I can change it for my purposes. Also, while this would be quite easy in VBA I am looking for an alternative solution. Many thanks.

I was wondering if is possible to find the maximum value in a list based upon specific criteria. For example, I have a long list of data and use some Vlookups to add week numbers based on the [dt]*[/dt] of the data values. I am then trying to find the maximum value of a certain week. Imagine three columns with the date, product, and revenue. By adding a fourth column I have the product and week no (derived from the date). Is there a function in excel which can find the maximum revenue of each week? Hope this makes sense. many thanks.

Hi, I have a couple of comboboxes - the contents of the second are dependent on the selection in the first one. I have this part running fine. the problem I am having is that when a new option is selected in the first combo box the contents of the second combobox do not change until the
dropbuttonclick event of the second.

To make things clear here is the order of operations:

user clicks the drop down arrow on combobox 1 > user then makes a selection from combobox 1 > this calls the code to populate the second combobox dependant on what user has selected > [THIS IS WHERE THE PROBLEM IS] - basically i want the first option of the new list to be automatically displayed in the second combobox now - however, the old value is left there until the user clicks the drop down arrow.

Hope this makes sense. Can anyone help me out?

Thanks for reading this.

Re: Retain Leading Zeros in VBA Array

Thanks for the input guys.
HOWDJ - that was what i figured but evidently not.
DAVE - thanks I'll try that now. I need these in an array because they are codes which cut up data for further work later on in my code.

Re: Treating numbers as string variables to preserve zeros at the beggining

Thanks for the suggestion. But still not working. This is a strange one.

Hi, I am just basically sorting through a huge dataset and grabbing unique
codes and storing them in an array for later use. Some of the codes are
numeric and some are alphanumeric therefore I am storing them as strings.
However, when a code is for example 000578 - once a recall this from the
array it has become 578. Is there anyway to preserve the entire code, i.e.
make the array recall 000578.
Thanks for any suggestions.

We have a load of workbooks and have run some code to remove all links.
This works fine when it is opened in 2003 (the version they were created
on), however when opened in excel 2000 the prompt comes to update the links.
Does anyone know what changes have been made in 2003 so that this occurs,
and also is there some code I can run on 2003 which will make it so that if
they are opened in 2000 this will not happen. hope this makes sense.
thanks

Re: GIF not behaving properly in userform

Thanks for the suggestion Ritchie. I tried that and now the first 'frame' of the GIF appears (previously no picture appeared) but it just remains static. A very perplexing problem - why does the breakpoint make it work, and if this is the only way to make it work is there a way to simulate a break point and then pressing F5?

Hi, I have some code to put a GIF animation into a web browser on a userform. This works fine - on its own. However, once the GIF has loaded I want to run a whole bunch of code in the background. If I run the code all at once, the userform appears but the picture doest show - but when I place a breakpoint between loading the userform and the main sub routine of my code - the GIF loads and the animation is shown all the time my code is running in the backgorund. Does anyone know why this doesnt work?

Hi, I am trying to make a XY scatter chart with Y error bars for each entry.
I have the code to collect three series for the chart, and also to draw the
chart and add the first series to it. However, I am not completely sure how
to add the next two series to the chart. If anyone could take a look at the
code and give me any suggestions I would be extremely gratefull. Thanks.

With ActiveSheet
        lngLastRow1 = Sheets("Sheet2").Range("H65536").End(xlUp).Row
        lngLastRow2 = Sheets("Sheet2").Range("I65536").End(xlUp).Row
        lngLastRow3 = Sheets("Sheet2").Range("J65536").End(xlUp).Row
        Set rngYValues1 = Sheets("Sheet2").Range("H1:H" & lngLastRow1)
        Set rngXValues1 = Sheets("Sheet2").Range("B1:B" & lngLastRow1)
        Set rngYValues2 = Sheets("Sheet2").Range("I" & lngLastRow1 + 1 & 
":I" & lngLastRow2)
        Set rngXValues2 = Sheets("Sheet2").Range("B" & lngLastRow1 + 1 & 
":B" & lngLastRow2)
        Set rngYValues3 = Sheets("Sheet2").Range("J" & lngLastRow2 + 1 & 
":J" & lngLastRow3)
        Set rngXValues3 = Sheets("Sheet2").Range("B" & lngLastRow2 + 1 & 
":B" & lngLastRow3)
        Set chtChart = .ChartObjects.Add(74, 68, 578, 293).Chart
    End With


    With chtChart
        .ChartType = xlXYScatter
        .SetSourceData rngYValues1, PlotBy:=xlColumns
        .Axes(xlValue).MajorGridlines.Delete
        .HasLegend = False
        With .Axes(xlValue)
            .MinimumScale = 0
            .MaximumScale = UBound(arrScore) + 2
        End With
        With .Axes(xlCategory)
            .MinimumScale = arrScore(LBound(arrScore)) - 2
            .MaximumScale = arrScore(UBound(arrScore)) + 2
            .TickLabels.NumberFormat = "0.00"
        End With
        With .SeriesCollection(1)
            .XValues = rngXValues1
            .ErrorBar Direction:=xlY, Include:=xlMinusValues, 
Type:=xlCustom, MinusValues:="={100}"
        End With
        With .ChartGroups(1)
            .Overlap = 0
            .GapWidth = 10
            .HasSeriesLines = False
            .VaryByCategories = False
        End With
    End With

Re: Populate listbox from an array

Thanks a lot

Re: Populate listbox from an array

I wonder what I am doing wrong. Here is a very simple example which gives me the same error. can you tell me what I have done wrong.

Hi, I know there must be a fairly simple solution to this one but I cant quite get the syntax right. Basically I have a set of values stored in an array an I simply want to populate the list box with these. one article on the microsoft website simply gave:

'Assign the array to the listbox
          ListBox1.List = LArray

However, I get an object required error. Can anyone help me out? Thanks.