user input "Rows to Repeat"

[Guest]

I'm writing a macro to create a formatted page that will print to my
specifications...the only part I can't figure out is:

When I ask the user if they need rows to repeat at top, how can they select
them manually and my macro still read it so that it can adjust the page setup
automatically?

Appreciate any help.

 

[Guest]

Try using application.InputBox specify the type as "8" this tells excel that
you're expecting a range.
Dim UR As Range 'UserRange
Set UR = Application.InputBox(Prompt:="Please Select Header Range", _
Title:="Some Title", Type:=8)
If UR Is Nothing Then 'User didn't select do something
MsgBox "You didn't select anything"
End If

application.InputBox("Select Range","Hey Stupid",,,,,,8).Address

 

[Guest]

Dim rng as Range
set rng = Nothing
On error resume next
set rng = Application.Inputbox("Select rows to repreat at top",type:=8)
On Error goto 0
if not rng is nothing then
' rows were selected
set rng = rng.EntireRow
' set the rows to repeat at top
end if

 

[Guest]

Just a heads up:

If the user doesn't select anything, the results is an error unless you have
error handling someplace you haven't shown.

when I ran your posted code, I got an error 424 object required.

 

[Guest]

Still getting error...am I trying to do something I shouldn't here is the
beginning of my sub...this is the only area I'm having a problem with...even
setting the: Dim myrows as Range isn't working...


Sub props()
Titles = MsgBox("Do any rows need to repeat?", vbYesNo, "Repeating Titles")
If Titles = vbYes Then
Set myrows = Application.InputBox(prompt:="Select Rows", Type:=8)
Set myrows = myrows.EntireRow
With ActiveSheet.PageSetup
.PrintTitleRows = myrows
.PrintTitleColumns = ""
End With
Else: With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
End If

 

[Guest]

let me expand...it says "Unable to set the PrintTitleRows property of the
page setup class"....

 

[Guest]

Sub props()
Dim myRows as Range
Titles = MsgBox("Do any rows need to repeat?", vbYesNo, "Repeating Titles")
If Titles = vbYes Then
Set myrows = Application.InputBox(prompt:="Select Rows", Type:=8)
Set myrows = myrows.EntireRow
With ActiveSheet.PageSetup
.PrintTitleRows = myrows.address(1,1,xlA1)
.PrintTitleColumns = ""
End With
Else: With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
End If

 

[Guest]

Tx for the correction Tom, I forgot that the InputBox does not just leave the
range as nothing when not used.