Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
page setup won't allow entering "rows to repeat at top" | Excel Discussion (Misc queries) | |||
"Rows to repeat at Bottom" | Excel Worksheet Functions | |||
Help, Office 2007 "Rows to Repeat at Top" | Excel Worksheet Functions | |||
Help !!! Office 2007 "Rows to Repeat at top" how ???? | Excel Discussion (Misc queries) | |||
"Rows to repeat at top" option under page setup | Excel Worksheet Functions |