Macro help request (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

> 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.

Reply | Quote

> 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.

Reply | Quote

Fixed up the print code from work.

Reply | Quote

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

Reply | Quote

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

Sub FinPrint()
    Application.Dialogs(xlDialogPrint).Show
End Sub

Reply | Quote

Application.Dialogs(xlDialogPrint).Show

duhooohhh

Reply | Quote

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

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

Reply | Quote

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 . 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

Reply | Quote

> 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

Reply | Quote