Posts by jamesparker_1

jamesparker_1 · Oct 12th 2006

Hi

Why is the below code not valid? I am using a userform with a cancel button and i cant it to copy and paste some lines on sheet Chart_Data as it unloads. But i just get the error

"Runtime Error '1004': Method 'Select' of object '_Worksheet' Failed"

Code

Private Sub Cancel_Click()


    Chart_Data.Select
    Chart_Data.Rows("6:7").Copy
    Chart_Data.Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False


    Unload Me   'Cancels Operation
    Exit Sub


End Sub

Display More

Thanks

James

jamesparker_1 · Oct 9th 2006

Re: Blank First Value On Table Included On Graph

Hi Guys!

Problem Solved! I was so confused why i was having such a problem so i Had a good look at the data and it seems that I'd been sent information with a single space in what appeared to be blank cells which was the cause of the problem!

Thanks

James!

jamesparker_1 · Oct 9th 2006

Re: Blank First Value On Table Included On Graph

No i appreciate the help, To behonest my code usually looks similar to someone using an axe to crack open an egg if you know what i mean.

I'll have a go at intergrating that into my program and see if works. It seems a weird problem to have though cause if the cell is blank then the cell is blank, full stop! I dont know why Excel isnt recognising that!

Thanks again for your help!!

jamesparker_1 · Oct 9th 2006

Re: Blank First Value On Table Included On Graph

Hi KJ!

That's great! I knew there was an option. It's weird tho! Although i have the option set up as 'Plot nothing'. Excel still plots it as zero when I create the graph and i have to go through and press 'Delete' on every blank cell to make the graph plot nothing. I cant understand why i would need to do that when the cells are already empty!!

Just Used you VBA code and Yeah, i think that is what i need too except for the same problem as above. It seems really odd to me!!!

Any ideas?

jamesparker_1 · Oct 9th 2006

Hi

This is something i'm trying to do in VBA but I would also love to know how to fix this problem when using excel without using VBA.

Basically, If you have a data table of January to December and January's entry is blank. When you produce a line graph, the line starts from zero and goes up to the value for february. Is there a way to tell excell to ignore January if it's blank and start at february rather than changing the Source range which i can't do for my VBA code that i've written.

thanks

James

jamesparker_1 · Oct 9th 2006

Re: .find To Find Exact Match

Yeah, that is what i needed! Thanks! Good tip about the F1 Help too. I think that'll help me solve more problems myself!

Thanks again!!

James

jamesparker_1 · Oct 9th 2006

Hi

I'm using the below VBA code:

Code

Dim c, DataRow
        With Data
            Set c = .Range("A5:A350").Find(KPI, LookIn:=xlValues)
            If Not c Is Nothing Then
                        DataRow = c.Row
            End If
        End With

Now, my problem is that If KPI is for example = "Favourite Hotel" and if i have data that looks like :

Favourite Hotel - London
Favourite Hotel - Cardiff
Favourite Hotel
Favourite Hotel - Birmingham

Then it seems to not look be looking for an exact match (e.g. Favourite Hotel) and rather is finding the first item in the list that contains the KPI string (E.g. Favourite Hotel - London).

How do i make it search for an exact match?

James

jamesparker_1 · Oct 6th 2006

Re: Defining Variable Data Row For Chart Series

Great! Just tried it and It worked! Might keep with the subroutine for this one as I can then use that in some other worksheets but it is interesting to know what the solution is!

Thanks again for your time, help and effort!

James

jamesparker_1 · Oct 6th 2006

Re: Defining Variable Data Row For Chart Series

Ok cool, I appreciate that as I too would love to learn what is going on.

I've attached the file below. Sheet Q4 is the one i have the Private code attached to. Basically, a user would double click on an event (in this case Event 1) the macro then searches for the Area and the Event in the data sheet (i did this be concatinating Area and Event on both sheets and searching for that value). When it finds it it copies the line to Chart data where it is all formated ready for the graph to be produced. I hope that is a clear enough explaination.

jamesparker_1 · Oct 6th 2006

Re: Defining Variable Data Row For Chart Series

Hi Andy

Thanks for your help but i dont want to waste your time on something if it works to the degree that i need.

Thanks though, as i Really appreciate your help.

James

jamesparker_1 · Oct 6th 2006

Re: Defining Variable Data Row For Chart Series

Ok, I tried to do some testing and i foud that if I use your code in a Subroutine and call it into the main Macro then it all works perfectly. But if the routine you proposed is created in the Private Sub Worksheet_BeforeDoubleClick main code then it errors.

It's not a problem to use the subroutine method but It does dispell the copy/format being a problem. Do you think it is a problem with having the code in a Private Sub Worksheet_BeforeDoubleClick macro?

jamesparker_1 · Oct 6th 2006

Re: Defining Variable Data Row For Chart Series

Hi Dave/Andy.

I meant it bugged out at the same point with regard to the first posting that andy supplied (see below) which he then incorporated into his further posting.

Quote from Andy Pope

try

[vba]ActiveChart.SeriesCollection(1).XValues = _
"='Chart Data'!R1C10:" & range("'Chart Data'!J1").End(xltoright).Address( , ,xlR1C1)[/vba]

Anyway, I have now tried Andy's code in my book1.xls and it works fine so It must be something happening due to the rest of my code.

I'm not sure if it is useful to include the full code of my macro (Hopefully i've done something obvious) but here goes...

Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


Application.ScreenUpdating = False


    If Target.Column <> 7 Then Exit Sub
    Cancel = True
  
    Dim MyName As String


    'Unhide worksheets
    Data.Visible = True
    Chart_Data.Visible = True


    
    'Clear Column Headers and Clear Graph data on Chart Data Sheet
    Chart_Data.Select
    Chart_Data.Rows("1:2").Select
    Selection.ClearContents
    Chart_Data.Range("A1").Select
    
    'Copy in Header row
    Data.Select
    Data.Rows("3:3").Copy
    Chart_Data.Select
    Chart_Data.Rows("1:1").Select
    Chart_Data.Paste
       
    'Copy in selected data
    Convalue = Chart_Data.Cells(Target.Row, 4).Address
    KPI = Range(Convalue).Value
    
        Dim c, DataRow
        With Data
            Set c = .Range("A5:A350").Find(KPI, LookIn:=xlValues)
            If Not c Is Nothing Then


                        DataRow = c.Row
            End If
        End With
        
    If DataRow = "" Then
        
        'Hide Worksheets
        Data.Visible = False
        Chart_Data.Visible = False
        
        Rags.Select
        MsgBox ("     No Data found")
    Else
        
        
        Data.Select
        Data.Rows(DataRow).Copy
        Chart_Data.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    
    
        Chart_Data.Select
        Chart_Data.Range("A1").Select
        Chart_Data.Range("G2").Copy
        Chart_Data.Rows("3:3").Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        With Selection.Interior
            .ColorIndex = 4
            .Pattern = xlSolid
        End With
        Selection.Font.ColorIndex = 0
        With Selection.Font
            .Name = "Arial"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
        End With
        Chart_Data.Range("H2").Copy
        Chart_Data.Rows("4:4").Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        With Selection.Font
            .Name = "Arial"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = 50
        End With
        Selection.Font.ColorIndex = 0
        With Selection.Interior
            .ColorIndex = 3
            .Pattern = xlSolid
        End With
        Chart_Data.Cells.EntireColumn.AutoFit
        Chart_Data.Range("A1").Select
    
    
        'Name Chart sheet to relevant Name (Myname)
        Chart_Data.Select
        Chart_Data.Range("G2").Select
        MyName = Left(ActiveCell, 31) 'Can only be 30 Characters long
    
    
        'Create Graph
        Charts.Add
        With ActiveChart
            .ChartType = xlLine
            ' clear formats fails if chart has no default data
            If .SeriesCollection.Count > 0 Then .PlotArea.ClearFormats
         
            ' remove any data plotted by active cell containing data
            Do While .SeriesCollection.Count > 0
                .SeriesCollection(1).Delete
            Loop
         
            'Data Applied to Graph
            With .SeriesCollection.NewSeries
                .XValues = _
                "='Chart Data'!R1C10:" & Range("'Chart Data'!J1").End(xlToRight).Address(, , xlR1C1)
             
                ' change R1 to R2 in order for address to be correct
                .Values = _
                "='Chart Data'!R2C10:" & Range("'Chart Data'!J2").End(xlToRight).Address(, , xlR1C1)
                .Name = "='Chart Data'!R2C4"
            End With
            'Green Threshold Applied to Graph
            If Chart_Data.Range("G2").Value > 0 Then
                With .SeriesCollection.NewSeries
                    .Values = _
                    "='Chart Data'!R3C10:" & Range("'Chart Data'!J3").End(xlToRight).Address(, , xlR1C1)
                    .Name = "='Chart Data'!R3C8"
                End With
            End If
         
            'Red Threshold Applied to Graph
            If Chart_Data.Range("H2").Value > 0 Then
                With .SeriesCollection.NewSeries
                    .Values = _
                    "='Chart Data'!R4C10:" & Range("'Chart Data'!J4").End(xlToRight).Address(, , xlR1C1)
                    .Name = "='Chart Data'!R4C8"
                End With
            End If
         
        End With
       
        'Format Data Line
        ActiveChart.SeriesCollection(1).Select
        With Selection.Border
            .Weight = xlMedium
            .LineStyle = xlContinuous
        End With


        'Green Threshold Applied to Graph
            ActiveChart.SeriesCollection(2).Select
            With Selection.Border
                .ColorIndex = 4
                .Weight = xlThick
                .LineStyle = xlContinuous
            End With
            With Selection
                .MarkerBackgroundColorIndex = xlNone
                .MarkerForegroundColorIndex = xlNone
                .MarkerStyle = xlNone
                .Smooth = False
                .MarkerSize = 3
                .Shadow = False
            End With
    
        'Red Threshold Applied to Graph
            ActiveChart.SeriesCollection(3).Select
            With Selection.Border
                .ColorIndex = 3
                .Weight = xlThick
                .LineStyle = xlContinuous
            End With
            With Selection
                .MarkerBackgroundColorIndex = xlNone
                .MarkerForegroundColorIndex = xlNone
                .MarkerStyle = xlNone
                .Smooth = False
                .MarkerSize = 3
                .Shadow = False
            End With


     
    
        ActiveChart.Location Where:=xlLocationAsNewSheet
        'ActiveChart.Location Where:=xlLocationAsObject, Name:="Q4" 'If you want graph on sheet
        With ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = Chart_Data.Range("E2") + " - " + Chart_Data.Range("G2")
            .Axes(xlCategory, xlPrimary).HasTitle = False
            .Axes(xlValue, xlPrimary).HasTitle = False
        End With


        'Adds "Close Graph" Box and assigns "Close_graph" Macro to Box
        ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 568.52, 52.02, _
        97.17, 36.67).Select
        Selection.Characters.Text = "Close Graph" & Chr(10) & "Click HERE"
        Selection.AutoScaleFont = False
        With Selection.Characters(Start:=1, Length:=18).Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
        End With
        Selection.AutoScaleFont = False
        With Selection.Characters(Start:=19, Length:=4).Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleSingle
            .ColorIndex = xlAutomatic
        End With
        Selection.ShapeRange.ScaleWidth 0.76, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.Fill.ForeColor.SchemeColor = 51
        Selection.ShapeRange.Fill.Visible = msoTrue
        Selection.ShapeRange.Fill.Solid
      
        Selection.ShapeRange.Fill.Visible = msoTrue
        Selection.ShapeRange.Fill.Solid
        Selection.ShapeRange.Fill.ForeColor.SchemeColor = 51
        Selection.ShapeRange.Fill.Transparency = 0#
        Selection.ShapeRange.Line.Weight = 0.75
        Selection.ShapeRange.Line.DashStyle = msoLineSolid
        Selection.ShapeRange.Line.Style = msoLineSingle
        Selection.ShapeRange.Line.Transparency = 0#
        Selection.ShapeRange.Line.Visible = msoTrue
        Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
        Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
      
        Selection.OnAction = "Close_Graph"
        ActiveChart.ChartArea.Select
    
        'Name Chart sheet to relevant Name (Myname) and Changes X-axis format
        ActiveChart.Select
        ActiveChart.Name = MyName
        
        With ActiveChart.Axes(xlCategory)
            .AxisBetweenCategories = False
            .TickLabels.NumberFormat = "mmm-yy"
        End With
        
        'Hide Worksheets
        Data.Visible = False
        Chart_Data.Visible = False


        Calculate
    End If
    
Application.ScreenUpdating = True
End Sub

Display More

jamesparker_1 · Oct 6th 2006

Re: Defining Variable Data Row For Chart Series

Hi Andy.

I still get the same error message and it debugs at the same point. Any ideas?

James

jamesparker_1 · Oct 5th 2006

Re: Defining Variable Data Row For Chart Series

ok So the relevant code is:

Code

'Create Graph
        Charts.Add
        ActiveChart.ChartType = xlLine
        ActiveChart.PlotArea.ClearFormats
        
        'Data Applied to Graph       
        ActiveChart.SeriesCollection(1).XValues = _
        "='Chart Data'!R1C10:" & Range("'Chart Data'!J1").End(xlToRight).Address(, , xlR1C1)
        ActiveChart.SeriesCollection(1).Values = _
        "='Chart Data'!R2C10:" & Range("'Chart Data'!J1").End(xlToRight).Address(, , xlR1C1)
        
        ActiveChart.SeriesCollection(1).Name = "='Chart Data'!R2C4"
        ActiveChart.SeriesCollection(2).Delete


        'Green Threshold Applied to Graph
        If Chart_Data.Range("G2").Value > 0 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(2).XValues = _
            "='Chart Data'!R3C10:" & Range("'Chart Data'!J3").End(xlToRight).Address(, , xlR1C1)
            ActiveChart.SeriesCollection(2).Name = "='Chart Data'!R3C8"
        End If
    
        'Red Threshold Applied to Graph
        If Chart_Data.Range("H2").Value > 0 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(3).XValues = _
            "='Chart Data'!R4C10:" & Range("'Chart Data'!J4").End(xlToRight).Address(, , xlR1C1)
            ActiveChart.SeriesCollection(3).Name = "='Chart Data'!R4C8"
       End If

Display More

jamesparker_1 · Oct 5th 2006

Re: Defining Variable Data Row For Chart Series

Hi Andy

When I use the above it goes into Debug mode and tells me Run time error 1004 Method 'Range' of object' _Worksheet' Failed

James

jamesparker_1 · Oct 5th 2006

Hi

I want the below code to run from R1C10 (i.e. cell J1) to the end of the data in the row, which will vary in position, rather than having to define where the end of the row will be beforehand (i.e. R1C21).

Is this possible?

Code

ActiveChart.SeriesCollection.NewSeries 
ActiveChart.SeriesCollection(1).XValues = "='Chart Data'!R1C10:R1C21"
ActiveChart.SeriesCollection(1).Values = "='Chart Data'!R2C10:R2C21" '

Thanks James

jamesparker_1 · Oct 4th 2006

Hi

I have a sheet that I protect but i use the below code so that I can still use my gouping '+' or '-' symbols to hide/unhide rows.

Code

ws.Protect Password:="PASSWORD", userinterfaceonly:=True
            ws.EnableOutlining = True

This is ok but When close the workbook and reopen it, my sheet becomes fully protected and i cant use the group icons.

Can anyone either suggest better code or a way to initilise the workbook to act in this way when it loads up.

For Reference Below is my full code:

Code

Sub ProtectAll()


    Dim ws As Worksheet
    sSheet = Control.Name


    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
        Case sSheet1
        Case Else
            ws.Protect Password:="PASSWORD", userinterfaceonly:=True
            ws.EnableOutlining = True
        End Select
    Next ws
   
End Sub

Display More

Thanks

James

jamesparker_1 · Sep 28th 2006

Re: Vba Mailmerge To Autoselect Sheet Of Table

Hi

Thanks So much for your help! It was really weird but i used the Macro recorder myself but i didnt get any of that information appear!

Thank you very much for your time and effort!

James

jamesparker_1 · Sep 27th 2006

Re: Unhide Sheet With Index Name

That Worked great! Thanks for your help on that one! That's exactly what i needed it to do!!

Thanks again

James

jamesparker_1 · Sep 27th 2006

Re: Unhide Sheet With Index Name

Hi.

No your not being thick. Its really hard to describe somethings over forums i find. And I appreciate your help loads!

I think we agree on the codename/index name being the thing that you cannot change, and the Sheet name being the name that u can edit on the tabs in excel.

So yes, i want to unhide a sheet based on its codename.But...

My sheets codenames are WF_Edin_2006,WF_Edin_2007,WF_Edin_2008 etc. Variable which the user selects called 'Year' and the user can select 2006, 2007, 2008 etc.

In VBA, I want to concatenate the string "WF_Edin_" with the variable 'Year' to give me the codename/index name of the sheet i want to unhide.

I then unhide the sheet based on its codename.

Posts by jamesparker_1

Select Sheet In Userform Cancel Button

Blank First Value On Table Included On Graph

Blank First Value On Table Included On Graph

Blank First Value On Table Included On Graph

Blank First Value On Table Included On Graph

Find In VBA Code: Find Exact Match

Find In VBA Code: Find Exact Match

Defining Variable Data Row For Chart Series

Defining Variable Data Row For Chart Series

Defining Variable Data Row For Chart Series

Defining Variable Data Row For Chart Series

Defining Variable Data Row For Chart Series

Defining Variable Data Row For Chart Series

Defining Variable Data Row For Chart Series

Defining Variable Data Row For Chart Series

Defining Variable Data Row For Chart Series

Retain Use Of Group/Outline on Protected Sheets/Worksheets

Vba Mailmerge To Autoselect Sheet Of Table

Unhide Sheets/Worksheet With Variable CodeNames

Unhide Sheets/Worksheet With Variable CodeNames