Results 1 to 38 of 38

Shrink and improve ActiveSheet Chart Objects code

Threaded View

Dragondude Shrink and improve... 11-29-2018, 12:54 AM
mjr veverka Re: Trying to shrink and... 11-29-2018, 09:52 AM
Dragondude Re: Trying to shrink and... 11-29-2018, 06:12 PM
Dragondude Re: Trying to shrink and... 11-29-2018, 11:34 PM
mjr veverka Re: Trying to shrink and... 11-30-2018, 05:21 AM
Dragondude Re: Trying to shrink and... 12-02-2018, 07:22 PM
Dragondude Re: Trying to shrink and... 11-29-2018, 08:37 PM
jeffreybrown Re: Trying to shrink and... 11-29-2018, 09:05 PM
Dragondude Re: Trying to shrink and... 11-29-2018, 09:35 PM
Dragondude Re: Shrink and improve... 12-03-2018, 11:24 PM
mjr veverka Re: Shrink and improve... 12-04-2018, 06:54 AM
Dragondude Re: Shrink and improve... 12-09-2018, 08:53 PM
mjr veverka Re: Shrink and improve... 12-10-2018, 06:37 AM
Dragondude Re: Shrink and improve... 12-10-2018, 06:37 PM
mjr veverka Re: Shrink and improve... 12-10-2018, 09:03 PM
Dragondude Re: Shrink and improve... 12-10-2018, 09:12 PM
Dragondude Re: Shrink and improve... 12-11-2018, 12:13 AM
mjr veverka Re: Shrink and improve... 12-11-2018, 07:22 AM
Dragondude Re: Shrink and improve... 12-11-2018, 06:11 PM
mjr veverka Re: Shrink and improve... 12-11-2018, 08:12 PM
Dragondude Re: Shrink and improve... 12-11-2018, 08:18 PM
mjr veverka Re: Shrink and improve... 12-13-2018, 01:39 PM
Dragondude Re: Shrink and improve... 12-13-2018, 09:24 PM
Dragondude Re: Shrink and improve... 12-13-2018, 06:05 PM
mjr veverka Re: Shrink and improve... 12-14-2018, 06:15 AM
Dragondude Re: Shrink and improve... 01-01-2019, 08:58 PM
mjr veverka Re: Shrink and improve... 01-02-2019, 03:10 PM
Dragondude Re: Shrink and improve... 01-02-2019, 06:01 PM
mjr veverka Re: Shrink and improve... 01-02-2019, 06:46 PM
Dragondude Re: Shrink and improve... 01-02-2019, 07:10 PM
mjr veverka Re: Shrink and improve... 01-02-2019, 07:30 PM
Dragondude Re: Shrink and improve... 01-02-2019, 07:39 PM
mjr veverka Re: Shrink and improve... 01-03-2019, 03:52 PM
Dragondude Re: Shrink and improve... 02-04-2019, 07:41 PM
Dragondude Re: Shrink and improve... 01-03-2019, 06:24 PM
Dragondude Re: Shrink and improve... 01-08-2019, 10:41 PM
Dragondude Re: Shrink and improve... 01-17-2019, 05:43 PM
Dragondude Re: Shrink and improve... 02-11-2019, 08:54 PM
  1. 11-29-2018, 12:54 AM #1
    Dragondude is offline
    Registered User
    Join Date
    11-29-2018
    Location
    Sunny Cairns, QLD AUST
    MS-Off Ver
    2010
    Posts
    97

    Shrink and improve ActiveSheet Chart Objects code

    Hi all,

    This is my first post here and I have only be playing with code for a week so be gentle.

    I have manage to create a monster. I have 28 buttons for 28 pieces of equipment. I have 31 specifications that need to be displayed in 31 line charts for each piece of equipment. As you can imagine a little bit of typing and glad I know how to cut and paste.

    The code works. Each button calls a macro and changes the colour of the button so you know which one is selected. I won't bable on anymore and will post the code.


    
Sub MacroDG1()
'
' MacroDG1 Macro
'

'
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A8:U8")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A9:U9")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A11:U11")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
  
    ActiveSheet.ChartObjects("Chart 4").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A12:U12")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A13:U13")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 6").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A14:U14")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 7").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A15:U15")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 8").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A17:U17")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 9").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A23:U23")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 10").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A28:U28")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 11").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A29:U29")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 12").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A30:U30")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
        
    ActiveSheet.ChartObjects("Chart 13").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A31:U31")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
        
    ActiveSheet.ChartObjects("Chart 14").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A32:U32")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
        
    ActiveSheet.ChartObjects("Chart 15").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A33:U33")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
        
    ActiveSheet.ChartObjects("Chart 16").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A34:U34")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"

    ActiveSheet.ChartObjects("Chart 17").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A35:U35")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 18").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A36:U36")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 19").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A37:U37")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 20").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A39:U39")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 21").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A40:U40")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 22").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A41:U41")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 23").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A44:U44")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 24").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A45:U45")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 25").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A46:U46")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
        
    ActiveSheet.ChartObjects("Chart 26").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A47:U47")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
        
    ActiveSheet.ChartObjects("Chart 27").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A48:U48")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
        
    ActiveSheet.ChartObjects("Chart 28").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A49:U49")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
        
    ActiveSheet.ChartObjects("Chart 29").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A57:U57")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"

    ActiveSheet.ChartObjects("Chart 30").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A58:U58")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
    
    ActiveSheet.ChartObjects("Chart 31").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Sheets("DG # 1").Range("A59:U59")
    ActiveChart.SeriesCollection(1).XValues = "='DG # 1'!$B$2:$U$2"
                
End Sub

    This one of the 28 macros dumping data from sheet DG # 1 into each chart.

    
Private Sub CommandButton2_Click()
    Call MacroDG1
    ActiveSheet.CommandButton1.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton2.BackColor = &H80000010     'dark grey
    ActiveSheet.CommandButton3.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton4.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton5.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton6.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton7.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton8.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton9.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton10.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton11.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton12.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton13.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton14.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton15.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton16.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton17.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton18.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton19.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton20.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton21.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton22.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton23.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton24.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton25.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton26.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton27.BackColor = &H8000000F     'grey
    ActiveSheet.CommandButton28.BackColor = &H8000000F     'grey
End Sub
    This is the code that calls a macro and changes the colour of the buttons

    This where the fun starts. In the range comments below the cells U8 and U$2 may be different for each piece of equipment, It is determined by the number of test sample taken/recorded. So the "U" may be an "M" or a "K" but for each of the 31 specs it will be the same.

    Range("A8:U8") (This cell location range has data in it.)

    "='DG # 1'!$B$2:$U$2" (This cell location range has dates in it. Possibly you could examine cells B2 to ??2 to see if it contains a date, not sure if this can be done, as soon as a cell doesn't have a date go back one and use it. This cell letter would also need to be applied to the data range as well.)

    Is there a way I can do an If/Case type of look up? At the moment this sort of thing is way above me so any help would be greatly appreciated.
    Last edited by jeffreybrown; 11-29-2018 at 09:03 PM.
    Reply With QuoteRegister To Reply
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Can I Improve This Code?
    By AHFoddeR in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-10-2013, 12:48 AM
  2. [SOLVED] To improve Efficiency of code, code running too long
    By andywsw in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-19-2012, 05:54 PM
  3. Anyone can shrink this code?
    By fucell in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-11-2011, 07:40 AM
  4. Code to shrink font size after the first 3 characters and spaces
    By trickyricky in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-15-2011, 09:57 AM
  5. Code shrink
    By khalid79m in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2008, 11:19 AM
  6. [SOLVED] adding code to shrink columns to reasonable width
    By Todd F. in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-25-2005, 02:05 PM
  7. Re: Improve code
    By rjamison in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-13-2005, 08:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1