Help with repeating macro's

Hi Dave and Welcome to MrExcel,

Here's a few suggestions that should make your workbook more streamlined and easier to maintain.

You mention getting system lockups and crashes when your workbook is open with other Excel workbooks linked to MS Project. External links, especially those with formulas can put heavy demands on the system. It's possible that the cause of your crashes lies in those other workbooks. The suggestions below should help, but they may not eliminate those problems.

1. In lieu of using ActiveX or Form Checkboxes (you have some of each), try using "Marlett Checkboxes" approach. That approach uses VBA to toggle font symbol characters to display either checkbox checked or checkbox unchecked.
This article on Ozgrid explains how to set this up with some example code.
Excel VBA: Tick Cell Upon Selection

2. As you suspected, there are better alternatives than having separate macros that are nearly identical for each button. Instead you can assign one Sub procedure to all those similar purpose buttons. That Sub can use Application.Caller to determine the location of the button that Called the Sub, then use that location to process the desired range of cells dynamically.

This thread has code examples using this approach.
http://www.mrexcel.com/forum/excel-...ow-relative-button-location-excel-2010-a.html

3. Your macros are stored in the ThisWorkbook module of your Workbook. It's a good practice to put this type of code in a Standard Code Module of your workbook, and limit code in the ThisWorkbook module to Event procedures that are triggered at the Workbook level (like your Sub Workbook_BeforeSave).

Just ask if you have any problems implementing those suggestions.

Hey Jerry,

Firstly thank you for your reply.

I've implemented the 'Marlett Checkbox' approach and that has made a real difference.

Unfortunately thought I'm too much of a novice at this sort of thing to use the add/remove line approach to achieve what I want to do.
My thought process was instead of 'CopyInsertThisRow' I need to use the column range I want added to the email body. Then instead of using '.copy' I would add the code from my original to sent the mail.

It's so frustrating to see what you want to do but lack the coding skills to carry it out.

Thanks again
Dave

For my 2 cents worth, I'd remove ALL the ready for despatch buttons and leave only 1.
That one would have an InputBox asking which chair is ready to send, the user selects the chair cell in column "B" and rest of your code would populate.
That'll get rid of a fair bit of code AND the buttons !

Further to the above.
You could attach this macro to 1 button....and get rid of the Rest of the RFD macros AND the RFD Buttons

Hi Micheal,

Thanks for your reply.

Thats great and works really well. Only problem I can see with that method is that only using one button I cant link the 'Marked as ready' check box. This would get confusing as to what has been marked as ready and what hasn't.

Also is there a way to not add the border of the cells to the email?

Thanks
Dave

You can still use the check box or Marlett for Ready fr Dist....just get rid of all the buttons and have one situated somewhere else on the page.
I know, change is a bit confusing, but beneficial in the end ...

Michael M said:

Further to the above.
You could attach this macro to 1 button....and get rid of the Rest of the RFD macros AND the RFD Buttons

Code:

Sub MM1()
Dim ans As Range
Set ans = Application.InputBox("Which Chair is ready for Despatch, please Select a cell in Column B", "Chair Selection", Type:=8)
If MsgBox("Do you wish to mark " & ans & " as ready for despatch?", vbYesNo) = vbNo Then Exit Sub
   ActiveSheet.Range("A3:D3").Select
   ActiveWorkbook.EnvelopeVisible = True
   With ActiveSheet.MailEnvelope
      .Introduction = "This wheelchair has passed final inspection and is cleared for delivery."
      .Item.To = ""
      .Item.CC = ""
      .Item.Subject = ans & " / " & ans.Offset(, 1) & " is ready for despatch"
      .Item.Send
   End With
End Sub

Click to expand...

Hi Michael,
I've been playing around with the above you posted and it is a much better way of doing things.
Only problem I'm having is the email body alway shows cells A3-D3 insead of cells A-D of whichever row is selected as ready with the input box.

Thanks you
Dave

don't have EXcel at the moment....try

Thanks for the lightening fast reply.

Unfortunately it doesn't work. It now changes the selected cell from the inputbox to 'H9:K9' which results in 'H9:K9' in the subject field & copies the whole sheet into the email body.