Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    custom button for histogram (2003)

    I was hoping I could record a macro that would open the Data Analysis --> Histogram Menu. Well, after doing this the following code was porvided:
    <pre>Sub Histogram()
    Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$O$19:$O$60") _
    , ActiveSheet.Range("$U$7"), ActiveSheet.Range("$Q$8:$Q$14"), False, False _
    , False, False
    End Sub
    </pre>


    All I want is to have something that will open the Histogram Window w/o having to go through the menus. I tried to use only... Application.Run "ATPVBAEN.XLA!Histogram"
    This gives me a Run-time error '1004': 'ATPVBAEN.XLA' could not be found. I don't want the entire block of the code above b/c my Input Range, Rin Range & Output Range change.

    thanks,
    jackal

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: custom button for histogram (2003)

    I don't know how to call the built-in dialog. You could create a UserForm that looks like the Histogram dialog, and use code like you recorded, but with arguments from the UserForm.

    BTW, you can display the Data Analysis dialog as follows:

    Application.CommandBars("Tools").Controls("Data Analysis...").Execute

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: custom button for histogram (2003)

    Instead of the ActiveSheet.Range(...), use ActiveSheet.Range("Input") , "Bin", and "Output", where Input, Bin, and Output are named ranges. Or, if only the bottom of $O$19:$O$60 range changes and there is a blank cell below that range, then the code below should work. Note, I'm on a machine that does not have the analysis tool-pac, so I cannot test this. In other words, if this does not work, then we need to know what ranges vary when you call this macro. HTH --Sam
    <pre> With ActiveSheet
    Application.Run "ATPVBAEN.XLA!Histogram", _
    .Range(.Range("O19"), .Range("O19").End(xlDown)), _
    .Range("$U$7"), .Range("$Q$8:$Q$14"), _
    False, False, False, False
    End With
    </pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: custom button for histogram (2003)

    Thanks to both of you for the posts. Sam, the data that I am currently studying has to be resorted several times, then histogram data is gathered on 3 different columns(AVE, MIN, MAX), thus changing Input range and Output location every time. Hans' suggestion will at least speed up the process.

    thanks,
    jackal

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: custom button for histogram (2003)

    If you can give us some sample fake data with a histogram & indicate how you pick the input, output & bin ranges, then I'm sure that we can give you a macro that dynamically computes these ranges regardless of the input data size. I've gotten the analysis toolpack installed now. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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