Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    20 Q's for today :-) (2002)

    Hi,
    Can I add to this code to place the cursor back in the same part of the userform, following the messagebox. So following the error message, delete the contents AND leave the cursor there.

    If Len(A_Your_Name) < 7 Or InStr(1, A_Your_Name, " ", 1) = 0 Then
    MsgBox " Both Your First and Surname should be entered in full "
    A_Your_Name.Value = ""
    Exit Sub
    End If

    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 20 Q's for today :-) (2002)

    Try:

    If Len(A_Your_Name) < 7 Or InStr(1, A_Your_Name, " ", 1) = 0 Then
    MsgBox " Both Your First and Surname should be entered in full "
    A_Your_Name.Value = ""
    A_Your_Name.SetFocus
    Exit Sub
    End If
    Jerry

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 20 Q's for today :-) (2002)

    Perfect, thanks.
    Q2. :-)

    I have a worksheet that is auto populated by formula's from an input sheet. There are 400 possible rows but the user may only input 36 data lines, so 367 rows would be blank. If there were 400, it would print to 6 pages, likewise if there were only 36, with 5 of them being blank. How can i overcome this?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 20 Q's for today :-) (2002)

    Hi Nathan

    You could use a sub to find the last used row in the range and setthe print area like so:

    <pre>Sub SetPrintArea()
    Dim LastRow

    LastRow = ActiveWorkbook.Sheets("sheet1").Range("A65536").En d(xlUp).Row

    ActiveSheet.PageSetup.PrintArea = "$A$1:$G$" & LastRow

    End Sub</pre>

    Jerry

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 20 Q's for today :-) (2002)

    Let's say that you want the print area to start in cell A1 and be 12 colums wide, and that the sheet is named Sheet1.

    First, select an arbitrary range, then select File | Print Area | Set Print Area.
    This creates a local name Print_Area.
    Select Insert | Name | Define...
    Click on the Print_Area name.
    Change its definition to

    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),12)

    Click Add, then OK.
    You now have a dynamic print area.

    Disadvantage: if you go into File | Page Setup and press OK, the definition of Print_Area will be converted to a fixed area.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 20 Q's for today :-) (2002)

    Jerry,

    That won't work if the cells contain formulas. End(xlUp) will stop at a cell containing a formula even if that formula returns a blank.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 20 Q's for today :-) (2002)

    Yep gotcha, I glossed over

    <hr>...auto populated by formula's from an input sheet...<hr>
    Jerry

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 20 Q's for today :-) (2002)

    I'm not set up to a printer at the minute so I am at a disadvantage. I have tried both ways and print preview still shows 6 pages on both methods.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 20 Q's for today :-) (2002)

    You can always set the print area manually. That might be easier if you're not able to get the other methods to work.

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 20 Q's for today :-) (2002)

    The print is set from a button command as part of other code, so I am hoping that this can be incorporated.

    Can Jerry's solution be adapted to:

    A1 to K__ and for the final row number, use the value of D9 (being the item count) plus 20 being the summary section at the top.?
    Thanks

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 20 Q's for today :-) (2002)

    ActiveSheet.PageSetup.PrintArea = "$A$1:$K$" & Range("D9") + 20

  12. #12
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 20 Q's for today :-) (2002)

    Thanks Hans, works perfect.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •