Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Change Cell Values
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Default Cell Values at Change TheDrescher Excel Worksheet Functions 2 April 28th 10 04:41 PM
How do I lock cell value even if other values change Loraise Excel Discussion (Misc queries) 1 December 19th 06 09:24 AM
Global change to cell values Karen McKenzie Excel Discussion (Misc queries) 2 July 5th 06 10:08 AM
how to change cell sign and values me[_8_] Excel Programming 3 May 8th 05 09:06 PM
How do I use a Combo Box to change values in a Cell. Havok Excel Programming 4 October 29th 03 08:43 PM


All times are GMT +1. The time now is 02:43 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement