DIALOG BOX DEFAULT LABELS

[ghamilton]

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,

 

[Charles Chickering]

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

 

[Martin Fishlock]

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