Posts by DMariotti

Carom,

sorry for the late response, every name requires the same amendment to the formula.

Thanks
Daniel

One thing I forgot to mention that may have an impact is that, all the named ranges in the workbook are used in Graphs/Charts. Not sure if this will make any difference.

Thanks Daniel

Sorry Carim,

I don't understand what you mean by
' Insert Test to properly Target your Named Ranges Thanks Daniel

Thanks Carim,

The ActiveWorkbook.Names... line falls over - it gives a run time error 1004. It says there is a problem with this formula.

Thanks for your help
Daniel

How do I update multiple named ranges with VBA. I have a workbook with approximately 750 named ranges. I need to update part of the formula in all the named ranges. For example,

Name: JumboDRL205 has a formula of =offset(KPIData!$D$288,0,0,1,Cover!$I$1). I need the cell reference: Cover!$I$1, changed to Cover$I$3 for all of the named ranges.

Thanks
Daniel

Re: Show Data Labels On Bars Of Bar Chart

Marc,

Is this what you want?. If it is then;

Right click Graph
Format Data Series
Data Labels
select Category

Then go to Format Data Labels
Select the Alignment you want (I went centre)
I changed the font to 8
Selected the Y axis and formated the label to the same colour as the background.

Daniel

Re: Overlay 2 Charts

Marc,
Is this what it is supposed to look like? If it is then I put the first graph on the third area and then moved the 2nd graph onto the third.

Daniel

Re: Use Dynamic Range In Pivot Table

Why not try,

=OFFSET(Log!$B$1,0,0,COUNTA(Log!$B:$B),22)

Daniel

Re: Worksheet Controls Text Direction Not Left to Right

Dave,

I originally did a search but could not find anything helpful. It only occurs on controls from the forms toolbar and only in a particular workbook. The basic question is how does one change the text direction of text on a control.

Thanks,

Re: Text Direction Forms

Thanks Sicarii,

What I mean't by abnormal is that why does the "normal'one workbook have the text in the right direction whereas the other one has it in a vertical direction but the overall settings are the same for both workbooks.

Daniel

When placing a control on a worksheet, the text in the label,option box etc controls is vertical and I can't find a way of changing the direction to left to right. It doesn't happen on all workbooks. I can put the control on a "normal" workbook and then copy it to the "abnormal" workbook but I would like to know why/how I can change the direction of the text.

Note:
Under Tools/Options/International the text direction states left to right

Thanks

Daniel

Re: Link Combobox To Pivot Table Page Field

The part that was causing me a problem was passing the variable from the userform to the pivot table so it updated the pivot table. I solved it by moving that part of the code from a standard module to the click event on the userform.

'Pivot tables to update with user selection
    Sheets("graphByDesc").PivotTables("PivotTable1").PivotFields("Description").CurrentPage = Product 
    Sheets("graphByDesc").PivotTables("PivotTable2").PivotFields("Description").CurrentPage = Product 
     
    Sheets("graphByDesc").PivotTables("PivotTable3").PivotFields("Description").CurrentPage = Product 
     
    ActiveWindow.SelectedSheets.PrintPreview

It works fine now.

Daniel

Re: Link Combobox To Pivot Table Page Field

Dave,

Here is all the code.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 16/08/2007 by United Group Rail
'
Dim endfile as long
'
'
  
  Sheets("sheet3").Select
  Range("a1").Select
 Range("a1").CurrentRegion.ClearContents
 
 'Gets source data for combobox
    Sheets("SourceData").Range("descsource").Copy
    
    Sheets("Sheet3").Range("A1").PasteSpecial xlValues
     
    If WorksheetFunction.CountA(Cells) > 0 Then
        endfile = Cells.Find(What:="*", After:=[A1], _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
 End If
 'Filters source data for unique values
    Range("A1:A" & endfile).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "B1"), Unique:=True
        
    Range("B1").CurrentRegion.Select
    Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
     
    'Columns("A:A").Delete Shift:=xlToLeft


    
    Sheets("GraphByDesc").Select
    
    'User to select product to graph
    Load UserForm2
UserForm2.Show




Set Product = UserForm2.ComboBox1
MsgBox Product


'Pivot tables to update with user selection
       Sheets("graphByDesc").PivotTables("PivotTable1").PivotFields("Description").CurrentPage = Product
    Sheets("graphByDesc").PivotTables("PivotTable2").PivotFields("Description").CurrentPage = Product
   
    Sheets("graphByDesc").PivotTables("PivotTable3").PivotFields("Description").CurrentPage = Product
    
    ActiveWindow.SelectedSheets.PrintPreview
      
    With ActiveSheet.PageSetup
       .LeftHeader = ""
        .CenterHeader = _
        "&""Times New Roman,Bold Italic""&14Delivery Performance by Product"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.15748031496063)
        .RightMargin = Application.InchesToPoints(0.15748031496063)
        .TopMargin = Application.InchesToPoints(0.62992125984252)
        .BottomMargin = Application.InchesToPoints(0.236220472440945)
        .HeaderMargin = Application.InchesToPoints(0.275590551181102)
        .FooterMargin = Application.InchesToPoints(0.15748031496063)
        '.PrintHeadings = False
        '.PrintGridlines = False
        '.PrintComments = xlPrintNoComments
        '.PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlPortrait
        '.Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        '.BlackAndWhite = False
        '.Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    ActiveWindow.SelectedSheets.PrintPreview
   
    Sheets("Menu").Select
End Sub

UserForm2 Code

Public Product As String




Private Sub CommandButton1_Click()


Product = ComboBox1.Text
'MsgBox "You have selected a " & product & " to graph. "
UserForm2.Hide


End Sub

Thanks for taking the time,

Daniel

Re: Link Combobox To Pivot Table Page Field

Dave,

The MsgBox gives the response as a string eg handbrake which is valid for the data that the pivot table uses.

Daniel

Re: Istext Or Isnumber Use With Alphanumeric Strings

Kenny,
They could both be text
try using the value function as follows
Cell B23:687968307=ISTEXT(Value(MID(RIGHT(B23,3),1,1)))

That should work

Daniel

Re: Combobox Variable To Pivot Table

Dave,

In the Userform I have declared it as such

Public product As String




Private Sub CommandButton1_Click()


product = ComboBox1.Text
'MsgBox "You have selected a " & product & " to graph. "
UserForm2.Hide


End Sub

Thanks,

Daniel

What I am tryng to do is pass the combobox selection to the pivot table current page field. The user makes a selection from the userform and the selecion is passed to three separate pivot tables. I don't want the user to get access to the pivot tables. The MsgBox indicates the correct selection but the pivot table doesn't accept it. It gives the follow error message;

"Unable to set the default property of the pivotItem class"

What am I doing wrong?

Thanks,

Daniel

Load UserForm2
UserForm2.Show
Set product = UserForm2.ComboBox1
MsgBox product


       Sheets("graphByDesc").PivotTables("PivotTable1").PivotFields("Description").CurrentPage = product
    Sheets("graphByDesc").PivotTables("PivotTable2").PivotFields("Description").CurrentPage = product
       Sheets("graphByDesc").PivotTables("PivotTable3").PivotFields("Description").CurrentPage = product

Re: Count Of Formulas In Workbook

Dave,

Thanks very much. It works great.

Daniel

I am reviewing a workbook and am trying to find out the number of formulae used. I thought using the specialcells and the count option on the status bar would give it to me but it doesn't return a value. I assume I could loop throught each workbook using the the special cells to return the value. How would I go about it in VBA?

Thanks,

Daniel

Re: Listbox Result To Pivottable Current Page Field

Dave,

Thanks for the response. The listbox result was to feed into three pivot tables that supplied the soucedata for three graphs. I wanted the user just to make one selection only from the listbox and then pickup the graphs from the printer without any other input. I thought that the listbox would return a variable that I could easily use in a procedure.

Regards Daniel