Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Aug 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro help request (Excel 2000)

    I have been asked to create a spreadsheet used by my fire department to track fire hydrant pressures and maintenance status. There is nothing really hi-tech about the spreadsheet, but I want to create a macro that will bring up a dialog box asking:
    1: Which shift (4 options, any or all could be selected)
    2: Which remarks (7 options)

    Then print the records that match the selections from the dialog box.

    The purpose of this is to print (in a firefighter proof way) a list of all the hydrants that need service to give to the water (or road) department. This list will need all of the fields in each record.

    I attached the working copy if you need to see what I
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help request (Excel 2000)

    Attached is your workbook with a button that calls a userform. The print button isnt coded as this is only an example of what could be done. The form would be cleaned up and you may consider placing the command in a menu as opposed to using a button on a form.


    Brent
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Aug 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help request (Excel 2000)

    That looks very much like what I was looking for... but how do I make it work? I would love to learn how to do things like this and if you could point me to a site that can teach a newbie, I would also appreciate that!

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

    Re: Macro help request (Excel 2000)

    Wayne, here is the macro that you need. I did not use a UserForm; it's alot for a "newbee" to comprehend. Instead I just put a button on the form. Here is the macro and I've also placed it in the attached workbook. HTH --Sam<pre>Option Explicit
    Sub SelectPrint()

    ' Get the shift
    Dim strShift As String
    strShift = Application.InputBox( _
    "Select shift (any cell in Column A)", _
    "Which Shift", , , , , , 8)
    If strShift = "False" Then Exit Sub

    ' Get the remark
    Dim strKey As String
    strKey = Application.InputBox( _
    "Select remark to find", _
    "Look for", , , , , , 8)
    If strKey = "False" Then Exit Sub

    ' Hide all but the shift with the remarks
    Dim i As Integer
    Application.ScreenUpdating = False
    For i = 1 To ActiveSheet.UsedRange.Rows.Count
    If Cells(i, 1).Value <> strShift Or _
    Cells(i, 11).Value <> strKey Then _
    Rows(i).Hidden = True
    Next i
    Rows(2).Hidden = False

    ' Print the sheet & unhide the rows
    ActiveSheet.PrintOut Copies:=1, Collate:=True
    Rows.Hidden = False
    Application.ScreenUpdating = True
    Range("A1").Select
    End Sub</pre>

    Attached Files Attached Files
    <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>

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help request (Excel 2000)

    Wayne,

    This site is a great one to learn from. There is a lot of great information and some very nice people who answer our questions.

    Both and <A target="_blank" HREF=http://www.j-walk.com/ss/excel/index.htm>John Walkenbach have good sites and Mr. Walkenbach has written a great many books on Excel.

    I have played with the userform some more. I am a novice and as such the code could be prettier but it works. Double-clicking the userform will restore the worksheet to normal. I can't test out any code sending the data to a printer as my inkjet is at work.

    Hope this helps,

    Brent
    Attached Files Attached Files

  6. #6
    New Lounger
    Join Date
    Aug 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help request (Excel 2000)

    I am going to be playing with that a bit to see how you did that! Could I ask for a refinement or two?

    First let me explain why I need it, you might see a better way of doing it.

    We have two stations (6 and 7) and two shifts per station (A and [img]/forums/images/smilies/cool.gif[/img]. Each shift/station is responsible for inspecting about 300 to 500 hydrants. They will update the portion of the spreadsheet relevant to their shift/station.

    When they are done, they will forward their portion to the Battalion Chief who will then order the needed repairs. He will print out lists of hydrants that need painting or weeds in front or whatever to be forwarded to the appropriate department.

    Here is what I may need changed:

    Can the Batt Chief print either one or all 4 of the shifts' bad hydrants? As it is (I think) He can only print one shift at a time.

    Similarly, can he print several types of conditions (i.e. Leaks, Out of Service) at the same time?

    One more question: In your macro... the line:

    strKey = Application.InputBox( _ "Select remark to find", _ "Look for", , , , , , 8)

    What are the commas for after "Look for"?

    Thanks! Really appreciate it!

    Wayne

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

    Re: Macro help request (Excel 2000)

    > Can the Batt Chief print either one or all 4 of the shifts' bad hydrants?

    You probably need to go with Brent's scheme, except use all check boxes, so you can check multiple items. Or you may want to use custom views (use the search link for the lounge to find info).

    > One more question ... InputBox.. commas for?
    While in VBA, place the cursor just after InputBox and press F1. Look at the help for the InputBox method.

    Kepp pluging & posting! --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>

  8. #8
    New Lounger
    Join Date
    Aug 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help request (Excel 2000)

    OK... I have played a bit and (unfortunately) I have been unable to make it do some more tricks. I have broken it a few times and fixed it though! Guess that's progress!

    When you open it now you will see a few changes on the userform. I tried to get the reset button to do what double-clicking does, but to no avail. Seems that putting the same text in after the command would work?

    Next I would like to print one (or all) of the conditions at the bottom of the userform. Again, I tried to make the code you wrote for selecting the shift to work, but I never figured it out.

    How do I make this available on all the sheets? Can I just copy/paste everything from sheet 2001 to all of the others, or do I need to do something in the project to make it seen?

    If you could help me on these I would appreciate it!

    Thanks!

    Wayne
    Attached Files Attached Files

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help request (Excel 2000)

    > I tried to get the reset button to do what double-clicking does, but to no avail. Seems that putting the same text in after the command would work?

    To get the reset button to work just copy the code from the userform_doubleclick procedure and paste it into the reset button click procedure. In fact with the reset button there is no need for the form double click...I used that while working on the code.

    > Next I would like to print one (or all) of the conditions at the bottom of the userform. Again, I tried to make the code you wrote for selecting the shift to work, but I never figured it out.

    I will work on it on Sunday. I still cant work on any print code as I get error messages without my printer connected. (my personal printer is at work).

    > How do I make this available on all the sheets? Can I just copy/paste everything from sheet 2001 to all of the others, or do I need to do something in the project to make it seen?

    You could create an add-in or create a custom menu item that is built and destroyed when the workbook is opened and closed.

  10. #10
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help request (Excel 2000)

    > When you open it now you will see a few changes on the userform. I tried to get the reset button to do what double-clicking does, but to no avail. Seems that putting the same text in after the command would work?

    I deleted the double_click event and placed the code in the reset button click event.

    > Next I would like to print one (or all) of the conditions at the bottom of the userform. Again, I tried to make the code you wrote for selecting the shift to work, but I never figured it out.

    The way I set it up it will show only those items in the remarks frame that are checked.

    > How do I make this available on all the sheets? Can I just copy/paste everything from sheet 2001 to all of the others, or do I need to do something in the project to make it seen?

    I set up a menu item that will show up wheneven the workbook is active.

    I put in some print code but cant test it with my printer being at work. I took my printer to work so I have a printer local to my desk as opposed to WALKING down the hall to the LAN printer.

    <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    Attached Files Attached Files

  11. #11
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help request (Excel 2000)

    Fixed up the print code from work.
    Attached Files Attached Files

  12. #12
    New Lounger
    Join Date
    Aug 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help request (Excel 2000)

    When I run the macro I get a Compile Error... Invalid use of property. The de###### highlights the word ".Dialogs" in the following snippet of code.:
    Sub FinPrint()
    Application.Dialogs (xlDialogPrint)
    End Sub

    Everything else appears to be working great! THis has sure been a great learning experience for me and will prove to be very useful for our fire department, I really appreciate the help!

    Wayne

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help request (Excel 2000)

    What do you want Excel to do with the dialog? Assuming you want to show it, then try:

    <pre>Sub FinPrint()
    Application.Dialogs(xlDialogPrint).Show
    End Sub
    </pre>

    Legare Coleman

  14. #14
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help request (Excel 2000)

    Application.Dialogs(xlDialogPrint)<font color=red>.Show</font color=red>

    duhooohhh

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

Posting Permissions

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