Anyone with any ideas on this???



"Selah West" wrote in message
...

Yikes, actually came into a glitch w/that code... these changes are made
for

the whole worksheet and I only want it to be done from row 44 to the end
of

the worksheet.



Let me try to explain, this is a Juvenile Center Daily Log worksheet. So
a

detention officer will put in the name of the juvenile, which cell they
are

staying in, what level they are, violation... and last entry they would
make

on them is in the comments field. Once they enter any information on that

row the date (column H) would need to be dated NOW (which would need to be

protected).



Further down in this worksheet is a Shift Summary section (this is the
last

code that you provided me with) . If a officer enters any information in

this field it should center accross column B through G and the NOW date
must

end up in column H too.



Perhaps I need to make 2 different range names one for the juvenile and
one

for the shift summary in order for this to work. I have no idea what I
need

to do in the code to get this working.



I hope I explained this better for you.



Maybe this?



Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

Set rng = Range(Target, Cells(Target.Row, "G"))

If Target.Column = 2 Then

Application.EnableEvents = False

With Target

If .Value < "" Then

.Offset(, 6).Value = Now

Else

.Offset(, 6).Value = ""

End If

End With

Application.EnableEvents = True

rng.HorizontalAlignment = xlCenterAcrossSelection

End If

End Sub





Gord



On Mon, 29 Jun 2009 16:24:01 -0700, PennyM

wrote:



Hi Gord,

I am working with Selah on this worksheet. What I did was removed
the

merged cells as you suggested. I then entered in some text in Row
44

Column

B, highlighed the cells that I wanted this text to be in (column B

through G)

r-clicked selected format cells, Alignment, selected center across

selection.

It worked perfectly. Thanks.



However, I am not sure how many rows a user will be inputting (could

only be

Row 44 or it could be from 44-100) is there a way to incorporate
some

additional code to achieve this formating as well as the code that
is

statted

below? I would also need the NOW date to show up in Column H for
this

as

well.



Does this make sense and/or is it possible?



Penny



"Gord Dibben" wrote:



You cannot have two events of the same type in a worksheet module.



Where are the merged cells?



Perhaps you could define a target range for the autofit code to
run

on that

is separate from the column 7 target range.



Then you could combine into one change event.



OR............get rid of those crappy problem-causing merged
cells.



Can you use center across selection instead?





Gord





On Thu, 25 Jun 2009 13:33:53 -0700, "Selah West"

wrote:



Gord,



Thank you for your help! There's only one thing...I have attached

two codes,

the one you gave me and I have also used a code which is the

following:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 7 Then

Application.EnableEvents = False

If Target.Value < "" Then

Target.Offset(, 1).Value = Now

Else

Target.Offset(, 1).Value = ""

End If

Application.EnableEvents = True

End If

End Sub



Now, when I type something in my cells, I get this "Microsoft

Visual Basic

Compile error: Ambiguous name detected: Worksheet_Change. These

code work

individually but not together...any suggestions?





"Gord Dibben" <gorddibbATshawDOTca wrote in message

.. .

Row Autofit will not work with merged cells unless you add
event

code to

your worksheet.



If you "MUST" use merged cells and you want to go that

route...............



See google search thread for code by Greg Wilson. Watch out
for

word wrap

in the URL which is all one line.



http://groups.google.com/group/micro...1c160cbeb27874





Gord Dibben MS Excel MVP



On Thu, 25 Jun 2009 10:13:11 -0700, "Selah West"

wrote:



I have merged cells, so it disables Autofit. I would like to
know

if there

is a way to use atuofit and have merged cells.



"JLGWhiz" wrote in message

...

Maybe on the menu bar click FormatRowAutofit





"Selah West" wrote in message

...

I have a worksheet that has a comments column that is
formatted

to wrap

text if needed for each cell. My problem is I need the row
to

automatically adjust to the height of what ever is entered in

that cell.

I

want to be able to use merged cells. Is there a VBA event
code

to make

this happen?



Using Excel 2007

Selah

As I pointed out a week ago...........there is too much contained in the
thread to follow what you need.

Re-post with a scaled down description of your needs and the code you are
currently using.


Gord Dibben MS Excel MVP

On Tue, 25 Aug 2009 08:34:31 -0700, "Selah West"
wrote:

Anyone with any ideas on this???



"Selah West" wrote in message
...

Yikes, actually came into a glitch w/that code... these changes are made
for

the whole worksheet and I only want it to be done from row 44 to the end
of

the worksheet.



Let me try to explain, this is a Juvenile Center Daily Log worksheet. So
a

detention officer will put in the name of the juvenile, which cell they
are

staying in, what level they are, violation... and last entry they would
make

on them is in the comments field. Once they enter any information on that

row the date (column H) would need to be dated NOW (which would need to be

protected).



Further down in this worksheet is a Shift Summary section (this is the
last

code that you provided me with) . If a officer enters any information in

this field it should center accross column B through G and the NOW date
must

end up in column H too.



Perhaps I need to make 2 different range names one for the juvenile and
one

for the shift summary in order for this to work. I have no idea what I
need

to do in the code to get this working.



I hope I explained this better for you.



Maybe this?



Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

Set rng = Range(Target, Cells(Target.Row, "G"))

If Target.Column = 2 Then

Application.EnableEvents = False

With Target

If .Value < "" Then

.Offset(, 6).Value = Now

Else

.Offset(, 6).Value = ""

End If

End With

Application.EnableEvents = True

rng.HorizontalAlignment = xlCenterAcrossSelection

End If

End Sub





Gord



On Mon, 29 Jun 2009 16:24:01 -0700, PennyM

wrote:



Hi Gord,

I am working with Selah on this worksheet. What I did was removed
the

merged cells as you suggested. I then entered in some text in Row
44

Column

B, highlighed the cells that I wanted this text to be in (column B

through G)

r-clicked selected format cells, Alignment, selected center across

selection.

It worked perfectly. Thanks.



However, I am not sure how many rows a user will be inputting (could

only be

Row 44 or it could be from 44-100) is there a way to incorporate
some

additional code to achieve this formating as well as the code that
is

statted

below? I would also need the NOW date to show up in Column H for
this

as

well.



Does this make sense and/or is it possible?



Penny



"Gord Dibben" wrote:



You cannot have two events of the same type in a worksheet module.



Where are the merged cells?



Perhaps you could define a target range for the autofit code to
run

on that

is separate from the column 7 target range.



Then you could combine into one change event.



OR............get rid of those crappy problem-causing merged
cells.



Can you use center across selection instead?





Gord





On Thu, 25 Jun 2009 13:33:53 -0700, "Selah West"

wrote:



Gord,



Thank you for your help! There's only one thing...I have attached

two codes,

the one you gave me and I have also used a code which is the

following:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 7 Then

Application.EnableEvents = False

If Target.Value < "" Then

Target.Offset(, 1).Value = Now

Else

Target.Offset(, 1).Value = ""

End If

Application.EnableEvents = True

End If

End Sub



Now, when I type something in my cells, I get this "Microsoft

Visual Basic

Compile error: Ambiguous name detected: Worksheet_Change. These

code work

individually but not together...any suggestions?





"Gord Dibben" <gorddibbATshawDOTca wrote in message

.. .

Row Autofit will not work with merged cells unless you add
event

code to

your worksheet.



If you "MUST" use merged cells and you want to go that

route...............



See google search thread for code by Greg Wilson. Watch out
for

word wrap

in the URL which is all one line.



http://groups.google.com/group/micro...1c160cbeb27874





Gord Dibben MS Excel MVP



On Thu, 25 Jun 2009 10:13:11 -0700, "Selah West"

wrote:



I have merged cells, so it disables Autofit. I would like to
know

if there

is a way to use atuofit and have merged cells.



"JLGWhiz" wrote in message

...

Maybe on the menu bar click FormatRowAutofit





"Selah West" wrote in message

...

I have a worksheet that has a comments column that is
formatted

to wrap

text if needed for each cell. My problem is I need the row
to

automatically adjust to the height of what ever is entered in

that cell.

I

want to be able to use merged cells. Is there a VBA event
code

to make

this happen?



Using Excel 2007