user input "Rows to Repeat"
 

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.

user input "Rows to Repeat"
 

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
--
Charles Chickering

"A good example is twice the value of good advice."


"jasminesy" wrote:

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.

user input "Rows to Repeat"
 

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

--
Regards,
Tom Ogilvy

"jasminesy" wrote:

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.

user input "Rows to Repeat"
 

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.

--
Regards,
Tom Ogilvy


"Charles Chickering" wrote:

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
--
Charles Chickering

"A good example is twice the value of good advice."


"jasminesy" wrote:

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.

user input "Rows to Repeat"
 

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


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"jasminesy" wrote:

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.

user input "Rows to Repeat"
 

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

"jasminesy" wrote:

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


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"jasminesy" wrote:

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.

user input "Rows to Repeat"
 

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


--
Regards,
Tom Ogilvy


"jasminesy" wrote:

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


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"jasminesy" wrote:

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.

user input "Rows to Repeat"
 

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

"A good example is twice the value of good advice."


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Charles Chickering" wrote:

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
--
Charles Chickering

"A good example is twice the value of good advice."


"jasminesy" wrote:

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.