Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row Height-expand if text wraps in a cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row Height-expand if text wraps in a cell
Hi Selah,
You might want to try this. It's something someone else created although I don't recall who it was but it expands merged cells when character length exceed the merged column width. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range Dim ProtectStatus As Boolean With Target If .MergeCells And .WrapText Then ProtectStatus = Me.ProtectContents If ProtectStatus Then Me.Unprotect "" Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False On Error Resume Next ma.MergeCells = False c.ColumnWidth = MrgeWdth c.EntireRow.AutoFit NewRwHt = c.RowHeight c.ColumnWidth = cWdth ma.MergeCells = True ma.RowHeight = NewRwHt cWdth = 0: MrgeWdth = 0 On Error GoTo 0 Application.ScreenUpdating = True If ProtectStatus Then Me.Protect "" End If End With End Sub "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: |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row Height-expand if text wraps in a cell
Was written by Greg Wilson as I noted in my original June 25th post to OP
Gord Dibben MS Excel MVP On Wed, 30 Sep 2009 11:46:07 -0700, Alphafiction wrote: Hi Selah, You might want to try this. It's something someone else created although I don't recall who it was but it expands merged cells when character length exceed the merged column width. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range Dim ProtectStatus As Boolean With Target If .MergeCells And .WrapText Then ProtectStatus = Me.ProtectContents If ProtectStatus Then Me.Unprotect "" Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False On Error Resume Next ma.MergeCells = False c.ColumnWidth = MrgeWdth c.EntireRow.AutoFit NewRwHt = c.RowHeight c.ColumnWidth = cWdth ma.MergeCells = True ma.RowHeight = NewRwHt cWdth = 0: MrgeWdth = 0 On Error GoTo 0 Application.ScreenUpdating = True If ProtectStatus Then Me.Protect "" End If End With End Sub "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: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
where text wraps in a cell, how can the row height be auto set? | Excel Worksheet Functions | |||
Row Height-expand if text wraps in a cell | Excel Worksheet Functions | |||
Row Height-expand if text wraps in a cell | Excel Programming | |||
Row Height-expand if text wraps in a cell | Excel Programming | |||
Row Height-expand if text wraps in a cell | Excel Worksheet Functions |