Change Cell Values
 

I figured out how to change the cell value via an input box. How can I write
this macro so that it will input the string in the cell before text already
there.

Here is what I have:

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
Worksheets("Detail").Range("A4").Value = ChangeMonth
Worksheets("Summary").Range("A4").Value = ChangeMonth

I'd like to add something like

ChangeMonth 2007

Thanks

Change Cell Values
 

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
With Worksheets("Detail").Range("A4")
.Value = ChangeMonth & " " & .Text
End with
With Worksheets("Summary").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
--
Regards,
Tom Ogilvy



"MacroLearning" wrote:

I figured out how to change the cell value via an input box. How can I write
this macro so that it will input the string in the cell before text already
there.

Here is what I have:

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
Worksheets("Detail").Range("A4").Value = ChangeMonth
Worksheets("Summary").Range("A4").Value = ChangeMonth

I'd like to add something like

ChangeMonth 2007

Thanks

Change Cell Values
 

Thank you. This worked great.

Another question please:

I tried to add the following statement to that procedure and am getting an
error. Can't figure how to bypass it.

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
With Worksheets("Detail").Range("A4")
..Value = ChangeMonth & " " & .Text
End With
With ActiveSheet
With Worksheets("Summary").Range("A4")
..Value = ChangeMonth & " " & .Text
End With
===With Active.Sheet.Range("C8").Value = ChangeMonth&.Text<<===
End With

"Tom Ogilvy" wrote:

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
With Worksheets("Detail").Range("A4")
.Value = ChangeMonth & " " & .Text
End with
With Worksheets("Summary").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
--
Regards,
Tom Ogilvy



"MacroLearning" wrote:

I figured out how to change the cell value via an input box. How can I write
this macro so that it will input the string in the cell before text already
there.

Here is what I have:

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
Worksheets("Detail").Range("A4").Value = ChangeMonth
Worksheets("Summary").Range("A4").Value = ChangeMonth

I'd like to add something like

ChangeMonth 2007

Thanks

Change Cell Values
 

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
With Worksheets("Detail").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
With Worksheets("Summary").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
With ActiveSheet.Range("C8")
.Value = ChangeMonth & " " & .Text
End With

--
Regards,
Tom Ogilvy


"MacroLearning" wrote:

Thank you. This worked great.

Another question please:

I tried to add the following statement to that procedure and am getting an
error. Can't figure how to bypass it.

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
With Worksheets("Detail").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
With ActiveSheet
With Worksheets("Summary").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
===With Active.Sheet.Range("C8").Value = ChangeMonth&.Text<<===
End With

"Tom Ogilvy" wrote:

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
With Worksheets("Detail").Range("A4")
.Value = ChangeMonth & " " & .Text
End with
With Worksheets("Summary").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
--
Regards,
Tom Ogilvy



"MacroLearning" wrote:

I figured out how to change the cell value via an input box. How can I write
this macro so that it will input the string in the cell before text already
there.

Here is what I have:

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
Worksheets("Detail").Range("A4").Value = ChangeMonth
Worksheets("Summary").Range("A4").Value = ChangeMonth

I'd like to add something like

ChangeMonth 2007

Thanks

Change Cell Values
 

You've been Great!

Why is it that when I try to use the chr / str combo in other places I get
an error? for example:

I figured out the footer by using the recorder, the error is 438: "Object
doesn't support this property or method"
'Works Great
Sheets("Detail").Select
With Worksheets("Detail").Range("A4")
..Value = ChangeMonth & " " & .Text
End With
With ActiveSheet.Range("C8")
..Value = ChangeMonth & " " & .Text
End With
With ActiveSheet.Range("D8")
..Value = ChangeMonth & " " & .Text
End With
'Change the Footer
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "Headcount by Title"
.CenterFooter = ""

'until here
' ////This is the code that won't work\\\\
.RightFooter = ChangeMonth & " " & .Text
'/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\//\

.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With


"Tom Ogilvy" wrote:

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
With Worksheets("Detail").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
With Worksheets("Summary").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
With ActiveSheet.Range("C8")
.Value = ChangeMonth & " " & .Text
End With

--
Regards,
Tom Ogilvy


"MacroLearning" wrote:

Thank you. This worked great.

Another question please:

I tried to add the following statement to that procedure and am getting an
error. Can't figure how to bypass it.

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
With Worksheets("Detail").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
With ActiveSheet
With Worksheets("Summary").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
===With Active.Sheet.Range("C8").Value = ChangeMonth&.Text<<===
End With

"Tom Ogilvy" wrote:

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
With Worksheets("Detail").Range("A4")
.Value = ChangeMonth & " " & .Text
End with
With Worksheets("Summary").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
--
Regards,
Tom Ogilvy



"MacroLearning" wrote:

I figured out how to change the cell value via an input box. How can I write
this macro so that it will input the string in the cell before text already
there.

Here is what I have:

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
Worksheets("Detail").Range("A4").Value = ChangeMonth
Worksheets("Summary").Range("A4").Value = ChangeMonth

I'd like to add something like

ChangeMonth 2007

Thanks

Change Cell Values
 

Ok, Figured it out.

Sheets("Detail").Select
With Worksheets("Detail").Range("A4")
..Value = ChangeMonth & " " & .Text
End With
With ActiveSheet.Range("C8")
..Value = ChangeMonth & " " & .Text
End With
With ActiveSheet.Range("D8")
..Value = ChangeMonth & " " & .Text
End With
With ActiveSheet.PageSetup
ActiveSheet.PageSetup.RightFooter = _
Format(Worksheets("Detail").Range("A4").Value)
.LeftFooter = "Headcount by Title"
End With

"MacroLearning" wrote:

You've been Great!

Why is it that when I try to use the chr / str combo in other places I get
an error? for example:

I figured out the footer by using the recorder, the error is 438: "Object
doesn't support this property or method"
'Works Great
Sheets("Detail").Select
With Worksheets("Detail").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
With ActiveSheet.Range("C8")
.Value = ChangeMonth & " " & .Text
End With
With ActiveSheet.Range("D8")
.Value = ChangeMonth & " " & .Text
End With
'Change the Footer
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "Headcount by Title"
.CenterFooter = ""

'until here
' ////This is the code that won't work\\\\
.RightFooter = ChangeMonth & " " & .Text
'/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\//\

.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With


"Tom Ogilvy" wrote:

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
With Worksheets("Detail").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
With Worksheets("Summary").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
With ActiveSheet.Range("C8")
.Value = ChangeMonth & " " & .Text
End With

--
Regards,
Tom Ogilvy


"MacroLearning" wrote:

Thank you. This worked great.

Another question please:

I tried to add the following statement to that procedure and am getting an
error. Can't figure how to bypass it.

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
With Worksheets("Detail").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
With ActiveSheet
With Worksheets("Summary").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
===With Active.Sheet.Range("C8").Value = ChangeMonth&.Text<<===
End With

"Tom Ogilvy" wrote:

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
With Worksheets("Detail").Range("A4")
.Value = ChangeMonth & " " & .Text
End with
With Worksheets("Summary").Range("A4")
.Value = ChangeMonth & " " & .Text
End With
--
Regards,
Tom Ogilvy



"MacroLearning" wrote:

I figured out how to change the cell value via an input box. How can I write
this macro so that it will input the string in the cell before text already
there.

Here is what I have:

Dim MonthYear As String, Title As String
Dim ChangeMonth As Variant
MonthYear = "Enter the Month and Year of this Report"
Title = "Update Month"
ChangeMonth = Application.InputBox(MonthYear, Title)
Worksheets("Detail").Range("A4").Value = ChangeMonth
Worksheets("Summary").Range("A4").Value = ChangeMonth

I'd like to add something like

ChangeMonth 2007

Thanks