SENDKEYS with vbYesNoCancel and Select Case

kiwimtnbkr · #1 05-29-2018, 11:09 PM

Quote:

Originally Posted by gmaxey

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

slaycock · #2 05-30-2018, 04:16 AM

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.

kiwimtnbkr · #3 05-30-2018, 03:03 PM

Quote:

Originally Posted by slaycock

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.

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
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
From all UPPER CASE to Proper Case	davers	Word	1	04-30-2009 12:41 PM