Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
DIALOG BOX DEFAULT LABELS
How can I modify this macro to default to the last label typed for
this input box? I only need to be able to override it, so I don't need to re type it every time I run it. Sub header() Period = InputBox("What Period?") DivName = InputBox("What Division?") title1 = InputBox("CENTER TITLE ROW 1") title2 = InputBox("CENTER TITLE ROW 2") With ActiveSheet.PageSetup .LeftHeader = Period & Chr(10) & DivName .CenterHeader = title1 & Chr(10) & title2 .RightHeader = Date .RightFooter = page End With End Sub Thanks, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
DIALOG BOX DEFAULT LABELS
You will need to add a global variable for each of the inputs that you would
like to have a default for. To do this place a "Public" statement at the top of the module (before any "Sub" or "Function" statements. Public strPeriod Sub Blah() Dim Period As String Period = InputBox("BlahBlah",,strPeriod) strPeriod = Period End Sub -- Charles Chickering "A good example is twice the value of good advice." " wrote: How can I modify this macro to default to the last label typed for this input box? I only need to be able to override it, so I don't need to re type it every time I run it. Sub header() Period = InputBox("What Period?") DivName = InputBox("What Division?") title1 = InputBox("CENTER TITLE ROW 1") title2 = InputBox("CENTER TITLE ROW 2") With ActiveSheet.PageSetup .LeftHeader = Period & Chr(10) & DivName .CenterHeader = title1 & Chr(10) & title2 .RightHeader = Date .RightFooter = page End With End Sub Thanks, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
DIALOG BOX DEFAULT LABELS
Hi try this:
Sub header() Dim pos As Integer With ActiveSheet.PageSetup If .LeftHeader < "" Then pos = InStr(1, .LeftHeader, Chr(10)) If pos 0 Then Period = Left(.LeftHeader, pos - 1) divname = Right(.LeftHeader, Len(.LeftHeader) - pos) End If End If If .CenterHeader < "" Then pos = InStr(1, .CenterHeader, Chr(10)) If pos 0 Then title1 = Left(.CenterHeader, pos - 1) title2 = Right(.CenterHeader, Len(.CenterHeader) - pos) End If End If End With Period = InputBox("What Period?", , Period) divname = InputBox("What Division?", , divname) title1 = InputBox("CENTER TITLE ROW 1", , title1) title2 = InputBox("CENTER TITLE ROW 2", , title2) With ActiveSheet.PageSetup .LeftHeader = Period & Chr(10) & divname .CenterHeader = title1 & Chr(10) & title2 .RightHeader = Date .RightFooter = Page End With End Sub It is easier to split the fuction up as look at this one and declare the variables: Sub SplitOnChr10(ByRef s As String, ByRef sl As String, ByRef sr As String) Dim pos As Integer pos = InStr(1, s, Chr(10)) If pos 0 Then sl = Left(s, pos - 1) sr = Right(s, Len(s) - pos) End If End Sub Sub header1() Dim Period As String, divname As String Dim title1 As String, title2 As String With ActiveSheet.PageSetup If .LeftHeader < "" Then SplitOnChr10 .LeftHeader, Period, divname End If If .CenterHeader < "" Then SplitOnChr10 .CenterHeader, title1, title2 End If End With Period = InputBox("What Period?", , Period) divname = InputBox("What Division?", , divname) title1 = InputBox("CENTER TITLE ROW 1", , title1) title2 = InputBox("CENTER TITLE ROW 2", , title2) With ActiveSheet.PageSetup .LeftHeader = Period & Chr(10) & divname .CenterHeader = title1 & Chr(10) & title2 .RightHeader = Date .RightFooter = Page End With End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. " wrote: How can I modify this macro to default to the last label typed for this input box? I only need to be able to override it, so I don't need to re type it every time I run it. Sub header() Period = InputBox("What Period?") DivName = InputBox("What Division?") title1 = InputBox("CENTER TITLE ROW 1") title2 = InputBox("CENTER TITLE ROW 2") With ActiveSheet.PageSetup .LeftHeader = Period & Chr(10) & DivName .CenterHeader = title1 & Chr(10) & title2 .RightHeader = Date .RightFooter = page End With End Sub Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DIALOG BOX DEFAULT LABELS | Excel Programming | |||
specify default path in dialog open box | Excel Programming | |||
print dialog box with 2 copies as default? | Excel Programming | |||
new dialog box-Change default display | Setting up and Configuration of Excel | |||
GetOpenFilename Dialog default path | Excel Programming |