Replies

Thanks for your response Hans. I had already searched the Lounge and found your reply to another post that mentioned this Labeler. I installed it but couldn’t find it anywhere – but I have now found where it ended up – under the Tools menu!! (Seems logical in hindsight).

I was right-clicking away on a data label on thechart and got frustrated, shut down Excel, restarted Excel, removed the Add-In, re-installed the Add-In and knew it had to be somewhere. And there it was in the Tools menu… Works as expected.

Now, does anyone know how to change the XY scatter chart’s data points to a cross (+ – instead of a diamond, square, dash, etc.)?

Thanks for the pointer, very much appreciated, Leigh

Thanks Hans, for your sage words of advice regarding shared workbooks. (A bit like Master Documents in Word? That was a dud for me. Never had a lot of success and actually corrupted about 6 chapters of a manual that a team was working on…)

OK, can you tell me more about multiple users of documents? For the workbook in question I just assumed that it needed to be shared but this is how it is to be used.

• Whilst more than one person may use the job register, they will generally be using a different worksheet.
  [/list]Does that cause any problems with more than one person opening it and then trying to save and close their session?

  What are your thoughts about separate workbooks with (dare I say it?) a “master workbook” that collates, via links, all the data? Individual sheets would be just linked to each of the line areas workbooks. Then only one person would need to open and view the “master” whilst individual line managers updated their own job registers.

  Would that work?

Steve

If you have checked for the obvious with AdAware, SpyBot, etc. then you might check that the HDD is not reached more than 80% capacity. The Windows swap file usage can have a bearing on the speed of applications. (You might want to limit it to a specific size rather than rubber banding.)

Also applications like MS Outlook and MS Access do use a lot of resources by themselves let alone when they are running at the same time.

Try reducing the number of applications actually running by using the TasK Manager to selectively stop them and note if there is any change in speed for the better. This may help identify the culprit.

Leigh

Note of Action

Unable to try the options suggested but won’t get a chance for about 2 weeks now. I’ll kepp the Lounge posted on the outcome.

Thanks Leif

I did a further web search on “uplink” and “ethernet” and “hub” and came across the sort of information you mention.

Regrettably, I will not be able to test this until next Monday as the office is now closed but will report on my success (or otherwise) then.

Again, many thanks…

Thanks (as always) Hans

Sorry for the slow response but I am a one-man band and sometimes I need to keep the wolves at bay with site visits.

A selection of the code on Ron de Bruin’s website set me on the right path.

I can’t imagine that my client will have to worry that the calculations sheet will accidentally be sent with the quote.

Thanks Hans

I always get the dialog to appear when I try the template from my office. But dialog box doesn’t appear at the client location and that is what perplexes me. Why (or what) would cause it not to appear. BTW the dialog (copy below for other readers) appears whether the file has been saved to disk or not.

Can I programmatically make sure that only the selection is e-mailed and therefore not have to rely on the appearance of the dialog?

If so, can you suggest the code that would do it?

Further to my previous post here is the relevant code to describe what is happening.

Code for the form to select object to print or e-mail:

Private Sub cmdOK_Click()
    If optQuoteInt = True Then
        Range("InternalCopy").PrintOut
        Unload Me
    ElseIf optQuoteCust = True Then
        Range("CustomerCopy").PrintOut
        Unload Me
    ElseIf optBoth = True Then
        Range("InternalCopy").PrintOut
        Range("CustomerCopy").PrintOut
        Unload Me
    ElseIf optEMail = True Then
        Call SendMail
        Unload Me
    End If
End Sub 

And the code to select the area to be e-mailed.

Sub SendMail()

    Sheets("Quote - e-mail version").Select
    Application.Goto Reference:="CustomerCopy"  'print area on customer sheet
    
End Sub

What should I be trying?

Thanks Andrew, I take your advice and agree that it would be best as an occasional add-in due to its expected limited use.

Thanks for your input…

Andrew, I must lead a sheltered life – but I must admit that I like what both you and Hans have offered!! Just brilliant and I am very appreciative of the insight I have gained from your replies.

Attachment is the revised version for any Loungers that are following this thread. It incorporates the features mentioned by both Hans and Andrew and a small cleanup to the userform and added a toolbar button to a new toolbar that is attached to the document. As before, I would suggest that the userform, module and the toolbar button be moved to your Normal.dot so that it can be available whenever you need to print partial label sheets.

It is not intended for use with the mailmerge command – although maybe it could be modified.

Attachment contains a macro suitable for selecting which labels you want to print from a sheet when the Avery (or whichever brand) label sheet has been partly used.

If you think the code is not efficiently written then by all means please post a reply. I am happy to see others suggestions.

To use this code I would think that you would open the MS Word attachment and copy the userform (frmLabelSelect) and the module (labelPrint) to your Normal.dot template. Then create a simple toolbar button to run the macro.

If you have any suggestions on improvements please post.

Thanks Hans,

That code was exactly what was needed – I did modify it to print the request and just to make sure that the labels template was closed without saving changes.

Do you think other Loungers would benefit from the whole code being posted?

I should give some more detail.
* This process is not intended for mail-merge.
* Currently there are a number of templates that match the label sheets with the required labels already on them.
* The labels are meant for product (cans/cartons) and are printed as required.
* Sometimes, not a whole sheet is used, and rather than waste it, currently the template is opened and the ‘not to be printed’ labels are changed to white font.
* The user will choose the label type from the left frame and then select the labels to print from the right frame checkboxes.

Currently, only the label types shown are in use. (ie 2 across by 2 down, 2 across by 4 down,… 3 across by 8 down) but the form can be modified to suit the actual template (read label sheet type).

Does that make it easier to understand?

Forgive me – having not used the Switchboard previously, I did not realise that although I could not work out how to tell it to run a Function, I could simply create a macro and call that.

Yes, it was simple, really, but it does help to spend a bit of time exploring features before reaching for the keyboard! Thanks.

Forgive me – having not used the Switchboard previously, I did not realise that although I could not work out how to tell it to run a Function, I could simply create a macro and call that.

Yes, it was simple, really, but it does help to spend a bit of time exploring features before reaching for the keyboard! Thanks.