Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 05-29-2018, 08:22 AM
slaycock slaycock is offline SENDKEYS with vbYesNoCancel and Select Case Windows 7 64bit SENDKEYS with vbYesNoCancel and Select Case Office 2016
Expert
 
Join Date: Sep 2013
Posts: 256
slaycock is on a distinguished road
Default

Greg



Two reasons

1. Ignorance (mea culpa). I am a self taught VBA'er so probably miss out on lots that could make my life easier as I don't realise what the alternatives are until I've been forced into a position where I have to explore alternatives.

2. Trying not to overload the OP ( a clutching a straws excuse).

3. Trying to make the process straighforward for the OP or anyone else who has limited experience at VBA.
Reply With Quote
  #17  
Old 05-29-2018, 08:33 AM
slaycock slaycock is offline SENDKEYS with vbYesNoCancel and Select Case Windows 7 64bit SENDKEYS with vbYesNoCancel and Select Case Office 2016
Expert
 
Join Date: Sep 2013
Posts: 256
slaycock is on a distinguished road
Default

Quote:
the loop check for the custom document property is replaced with a process of trying to access the item explicitly
This was a deliberate decision. I'm a firm believer in reserving 'on error' for issues out of my control. Of course life would be much easier if VBA collections had an Exists property in the same manner as Scripting.Dictionaries.

It also looks like I have some reading to do tonight regarding tags.
Reply With Quote
  #18  
Old 05-29-2018, 06:56 PM
gmaxey gmaxey is offline SENDKEYS with vbYesNoCancel and Select Case Windows 7 32bit SENDKEYS with vbYesNoCancel and Select Case Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,422
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

Ok, but can I ask why? To a lot of people, the word "Error" carries connotations of bad or a mistake. The computer makes no such distinction. Error is just a condition as is No Error.

Just for the sake of an example, lets assume each grain of sand on the beach is etched with a unique ID. You need to find (if it exists) and pick up the grain with the ID "1234"

There is the loop, which works but could take a tremendously long time:

For Each oGrain In Beach.Grains
If oGrain.ID = "1234" Then
'Whatever"
Exit For
End If
Next

Or the explicit Set method using an Error handler. Always fast.
On Error Resume Next
Set oGrain = Beach.Grains("1234")
On Error GoTo 0
If Not oGrain is Nothing Then
'Whatever
End If
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #19  
Old 05-29-2018, 07:57 PM
kiwimtnbkr kiwimtnbkr is offline SENDKEYS with vbYesNoCancel and Select Case Windows 10 SENDKEYS with vbYesNoCancel and Select Case Office 2010 64bit
Advanced Beginner
SENDKEYS with vbYesNoCancel and Select Case
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

hi Greg,

Thanks for the code but it throws a 'Compile error: User-defined type not defined" as soon as it gets to

Dim oFrm As frmPrintDialog
Reply With Quote
  #20  
Old 05-29-2018, 08:13 PM
gmaxey gmaxey is offline SENDKEYS with vbYesNoCancel and Select Case Windows 7 32bit SENDKEYS with vbYesNoCancel and Select Case Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,422
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

You will have to change the name of your form.

i.e., change the existing form named ufrmPrintNumberedCopies to frmPrintDialog.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #21  
Old 05-29-2018, 10:53 PM
kiwimtnbkr kiwimtnbkr is offline SENDKEYS with vbYesNoCancel and Select Case Windows 10 SENDKEYS with vbYesNoCancel and Select Case Office 2010 64bit
Advanced Beginner
SENDKEYS with vbYesNoCancel and Select Case
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

Quote:
Originally Posted by slaycock View Post
No. That's not the answer.

Replace my_form.txtPages with my_form.txtPages.value

That works on my PC
Adjusted as suggested but it's still attempting to print the entire document rather than the inputted page range.

One other issue is that it isn't updating the linked data - I can manually update the document thru another macro but it's not happening as part of the print routine even though it looks like the options are there for it.

Would it help if I make the word doc and excel workbook available?
Reply With Quote
  #22  
Old 05-29-2018, 11:09 PM
kiwimtnbkr kiwimtnbkr is offline SENDKEYS with vbYesNoCancel and Select Case Windows 10 SENDKEYS with vbYesNoCancel and Select Case Office 2010 64bit
Advanced Beginner
SENDKEYS with vbYesNoCancel and Select Case
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

Quote:
Originally Posted by gmaxey View Post
You will have to change the name of your form.

i.e., change the existing form named ufrmPrintNumberedCopies to frmPrintDialog.
Sorted - changed the existing forms to frmPrintNumberedCopiesAll and frmPrintNumberedCopiesSelect and then adjusted the two lines in your code from frmPrintDialog to frmPrintNumberedCopiesAll and frmPrintedNumberedCopiesSelect respectively.

The validation works bang on for the PrintNumberedCopiesAll but only kind of works for the PrintNumberedCopies select which has a 3rd text box entitled txtPages in that it validates.

I managed to adjust your code to take into account the txtPages validation but somewhat disconcertingly I am unable to put commas and hyphens into the 'what pages require printing' box to assist with specifying what pages I wanted printed.
Code:
Option Explicit
Dim m_bTest As Boolean
Private Sub txtStartNumber_Change()
  Validate_OK
End Sub
Private Sub txtPages_Change()
  Validate_OK
End Sub
Private Sub txtPages_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  m_bTest = IsInteger(KeyAscii)
  If m_bTest = False Then
    Beep
    KeyAscii = 0
  End If
End Sub
Private Sub txtStartNumber_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  m_bTest = IsInteger(KeyAscii)
  If m_bTest = False Then
    Beep
    KeyAscii = 0
  End If
End Sub
Private Sub txtCopies_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  m_bTest = IsInteger(KeyAscii)
  If m_bTest = False Then
    Beep
    KeyAscii = 0
  End If
End Sub
Private Sub txtCopies_Change()
  Validate_OK
End Sub
Function IsInteger(ByVal i As String) As Boolean
  Select Case i  'Checking to see if inside valid Ascii range for integers
    Case 48 To 57: IsInteger = True
    Case Else: IsInteger = False
  End Select
End Function
Private Sub UserForm_Activate()
  Validate_OK
End Sub
Private Sub cmdCancel_Click()
  Tag = "CANCX"
  Hide
End Sub
Private Sub cmdOK_Click()
  Hide
End Sub
Private Sub Validate_OK()
  cmdOK.Enabled = True
  If Not IsNumeric(Trim(txtCopies)) Then cmdOK.Enabled = False
  If Not IsNumeric(Trim(txtStartNumber)) Then cmdOK.Enabled = False
  If Not IsNumeric(Trim(txtPages)) Then cmdOK.Enabled = False
End Sub
Reply With Quote
  #23  
Old 05-30-2018, 04:16 AM
slaycock slaycock is offline SENDKEYS with vbYesNoCancel and Select Case Windows 7 64bit SENDKEYS with vbYesNoCancel and Select Case Office 2016
Expert
 
Join Date: Sep 2013
Posts: 256
slaycock is on a distinguished road
Default

Quote:

Adjusted as suggested but it's still attempting to print the entire document rather than the inputted page range.

One other issue is that it isn't updating the linked data
Try adding the ranges option to the printout statement. The help for the printout statement isn't entirely clear as both the pages and range option are 'optional'. It doesn't state that ranges must be set if pages is used but its worth a try.

If fields are not updating at print then something is odd. We can try to rectify this by updating fields in the document before printing each copy as in your original code.

Replace the print loop with the following and let us know how you get on.

Code:
    For copy_number = first_copy To last_copy
        With ActiveDocument
            .CustomDocumentProperties(cdp_name) = CStr(copy_number)
            .Fields.Update
            .PrintOut copies:=1, Range:=wdPrintRangeOfPages, pages:=my_form.txtPages.Value
        End With
    Next
I checked this by printing to pdf on on my pc and all works as expected. It may not work for you if your field is in a header or footer.
Reply With Quote
  #24  
Old 05-30-2018, 04:38 AM
slaycock slaycock is offline SENDKEYS with vbYesNoCancel and Select Case Windows 7 64bit SENDKEYS with vbYesNoCancel and Select Case Office 2016
Expert
 
Join Date: Sep 2013
Posts: 256
slaycock is on a distinguished road
Default

Quote:
Ok, but can I ask why?
I suspect its the simple fact that in VBA handling errors is messy and not immune to user error.

I am embarrased to say I have wasted hours on a number of occasions trying to figure out why code was stopping and highlighting an error when using an on error statement.

In each case it was because I'd been debugging something else previously and had left the IDE error trapping option set to 'Break on all errors' .

Your point about the length of time the loop could take is quite valid and if this was a problem then I'd probably resort to investigating an alternative method such as the one you suggest.

PS I read up on the tag property for forms. Its interesting the neither of the two books I have on VBA programming mention this at all. I can see its value but my preference is to use a specific and well named property. In this case, on reflection, I would probably change the result property name to ValidatedOK, but that a personal preference.
Reply With Quote
  #25  
Old 05-30-2018, 06:33 AM
gmaxey gmaxey is offline SENDKEYS with vbYesNoCancel and Select Case Windows 7 32bit SENDKEYS with vbYesNoCancel and Select Case Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,422
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

Kiwi,

I didn't try to validate txtPages as I thought if left blank (i.e., not is numeric) then you would be printing all pages. As for entering the comma, dash and space that is due to the KeyPress event code. Change it to:

Code:
Private Sub txtPages_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  m_bTest = IsInteger(KeyAscii)
  If m_bTest = False Then
    Select Case KeyAscii
      Case Is = 32, 44, 45
      Case Else
        Beep
        KeyAscii = 0
    End Select
    Else
      Beep
      KeyAscii = 0
  End If
End Sub
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #26  
Old 05-30-2018, 03:03 PM
kiwimtnbkr kiwimtnbkr is offline SENDKEYS with vbYesNoCancel and Select Case Windows 10 SENDKEYS with vbYesNoCancel and Select Case Office 2010 64bit
Advanced Beginner
SENDKEYS with vbYesNoCancel and Select Case
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

Quote:
Originally Posted by slaycock View Post
Try adding the ranges option to the printout statement. The help for the printout statement isn't entirely clear as both the pages and range option are 'optional'. It doesn't state that ranges must be set if pages is used but its worth a try.

If fields are not updating at print then something is odd. We can try to rectify this by updating fields in the document before printing each copy as in your original code.

Replace the print loop with the following and let us know how you get on.

Code:
    For copy_number = first_copy To last_copy
        With ActiveDocument
            .CustomDocumentProperties(cdp_name) = CStr(copy_number)
            .Fields.Update
            .PrintOut copies:=1, Range:=wdPrintRangeOfPages, pages:=my_form.txtPages.Value
        End With
    Next
I checked this by printing to pdf on on my pc and all works as expected. It may not work for you if your field is in a header or footer.
The print functionality is now working absolutely bang on - THANK YOU!

However, whilst the fields (read serial number fields) are updating as excepted and always have been (sorry I think I've added to the confusion here), the links aren't. The idea behind the links updating is to make sure that the embedded tables in the document are updated with the latest information from the spreadsheet that it is linked to prior to printing.

I've gone back to my very original code and confirmed that the updating of the links still works which it does so I'm at a loss as to why your code with contains pretty much the same coding doesn't run the updating of the links.

Still happy to share the word doc and spreadsheet workbook if necessary.

EDIT - have now got the embedded tables in the document updating as expected when printing the entire document by adding 'Activedocument.Fields.Update' before the 'ActiveDocument.Printout Copies:=1' line of code.

Worked as expected when printing just selected pages from the document so initially had me scratching my head but the answer was obvious when I stepped back and had a proper look.

Now to put it into the production environment and check that it works as I expect it to.

Last edited by kiwimtnbkr; 05-30-2018 at 05:42 PM.
Reply With Quote
  #27  
Old 05-30-2018, 04:02 PM
kiwimtnbkr kiwimtnbkr is offline SENDKEYS with vbYesNoCancel and Select Case Windows 10 SENDKEYS with vbYesNoCancel and Select Case Office 2010 64bit
Advanced Beginner
SENDKEYS with vbYesNoCancel and Select Case
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

Quote:
Originally Posted by gmaxey View Post
Kiwi,

I didn't try to validate txtPages as I thought if left blank (i.e., not is numeric) then you would be printing all pages. As for entering the comma, dash and space that is due to the KeyPress event code. Change it to:

Code:
Private Sub txtPages_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  m_bTest = IsInteger(KeyAscii)
  If m_bTest = False Then
    Select Case KeyAscii
      Case Is = 32, 44, 45
      Case Else
        Beep
        KeyAscii = 0
    End Select
    Else
      Beep
      KeyAscii = 0
  End If
End Sub
so the one frm would cover both printing the entire document and a selection of pages?
Reply With Quote
  #28  
Old 05-30-2018, 11:27 PM
slaycock slaycock is offline SENDKEYS with vbYesNoCancel and Select Case Windows 7 64bit SENDKEYS with vbYesNoCancel and Select Case Office 2016
Expert
 
Join Date: Sep 2013
Posts: 256
slaycock is on a distinguished road
Default

Is everything working OK now. If not then I'm happy to look at your doc and worksheet.
Reply With Quote
  #29  
Old 05-30-2018, 11:39 PM
kiwimtnbkr kiwimtnbkr is offline SENDKEYS with vbYesNoCancel and Select Case Windows 10 SENDKEYS with vbYesNoCancel and Select Case Office 2010 64bit
Advanced Beginner
SENDKEYS with vbYesNoCancel and Select Case
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

It certainly appears to be but haven't been able to, thanks to working in a Citrix networked printer environment, get the duplex printer functionality to work due to changing session numbers.

I didn't have a chance to fully test it today but hope to do so tomorrow but so far so good so a MASSIVE thank you!

Will let you know how it goes tomorrow.

cheers muchly
Mike
Reply With Quote
  #30  
Old 05-31-2018, 04:22 AM
gmaxey gmaxey is offline SENDKEYS with vbYesNoCancel and Select Case Windows 7 32bit SENDKEYS with vbYesNoCancel and Select Case Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,422
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

so the one frm would cover both printing the entire document and a selection of pages?

Yes. That was the intent. If that field were left blank then print all pages. However, to save my own paper, like slaycock, I didn't test the actual print statement so it would likely need a tweak.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Case and Select case brent chadwick Word VBA 34 10-18-2015 02:13 PM
Replace & case Jennifer Murphy Word 1 02-11-2013 03:26 AM
SENDKEYS with vbYesNoCancel and Select Case A macro that can insert FILENAME, sendkeys CTRL ALT T, paste clipboard, and nextline kyjac85 Word VBA 13 09-20-2012 05:00 PM
Problem with the sendkeys in Win7 vidyapakki Excel Programming 1 05-07-2012 11:10 PM
SENDKEYS with vbYesNoCancel and Select Case From all UPPER CASE to Proper Case davers Word 1 04-30-2009 12:41 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:39 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft