Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calling module procedure from sheet procedure
Newbie non developer here...
I have created a procedure "AutoFitMergedCellRowHeight()" in Module1 that I can manually run against a field in a spreadsheet that that successfully resizes a cell height depending on the amount of text entered: Sub AutoFitMergedCellRowHeight() Dim CurrentRowHeight As Single, MergedCellRgWidth As Single Dim CurrCell As Range, RangeWidth As Single Dim ActiveCellWidth As Single, PossNewRowHeight As Single If ActiveCell.MergeCells Then With ActiveCell.MergeArea If .Rows.Count = 1 And .WrapText = True Then Application.ScreenUpdating = False CurrentRowHeight = .RowHeight ActiveCellWidth = ActiveCell.ColumnWidth RangeWidth = .Width For Each CurrCell In Selection MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth Next .MergeCells = False .Cells(1).ColumnWidth = MergedCellRgWidth While .Cells(1).Width < RangeWidth .Cells(1).ColumnWidth = .Cells(1).ColumnWidth + 0.5 Wend .Cells(1).ColumnWidth = .Cells(1).ColumnWidth - 0.5 .EntireRow.AutoFit PossNewRowHeight = .RowHeight .Cells(1).ColumnWidth = ActiveCellWidth .MergeCells = True .RowHeight = IIf(CurrentRowHeight PossNewRowHeight, _ CurrentRowHeight, PossNewRowHeight) End If End With End If End Sub I have also created a procedure under Sheet4: Sub Worksheet_Change(ByVal Target As Range) 'MsgBox "You just changed " & Target.Address Call Module1.AutoFitMergedCellRowHeight End Sub I effective want a cell to automatically resize itself after the cell value has changed. The manually triggered resize works fine but the auto resize does not. Any insight would be appreciated. EricLCTCS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling module procedure from sheet procedure
This Greg Wilson worksheet event code does the trick.
Just make sure merged cells are pre-formatted to wrap text. 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 With Target If .MergeCells And .WrapText Then 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 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 Application.ScreenUpdating = True End If End With End Sub Gord On Thu, 19 Jun 2014 17:23:29 +0100, EricLCTCS wrote: Newbie non developer here... I have created a procedure "AutoFitMergedCellRowHeight()" in Module1 that I can manually run against a field in a spreadsheet that that successfully resizes a cell height depending on the amount of text entered: Sub AutoFitMergedCellRowHeight() Dim CurrentRowHeight As Single, MergedCellRgWidth As Single Dim CurrCell As Range, RangeWidth As Single Dim ActiveCellWidth As Single, PossNewRowHeight As Single If ActiveCell.MergeCells Then With ActiveCell.MergeArea If .Rows.Count = 1 And .WrapText = True Then Application.ScreenUpdating = False CurrentRowHeight = .RowHeight ActiveCellWidth = ActiveCell.ColumnWidth RangeWidth = .Width For Each CurrCell In Selection MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth Next MergeCells = False Cells(1).ColumnWidth = MergedCellRgWidth While .Cells(1).Width < RangeWidth Cells(1).ColumnWidth = .Cells(1).ColumnWidth + 0.5 Wend Cells(1).ColumnWidth = .Cells(1).ColumnWidth - 0.5 EntireRow.AutoFit PossNewRowHeight = .RowHeight Cells(1).ColumnWidth = ActiveCellWidth MergeCells = True RowHeight = IIf(CurrentRowHeight PossNewRowHeight, _ CurrentRowHeight, PossNewRowHeight) End If End With End If End Sub I have also created a procedure under Sheet4: Sub Worksheet_Change(ByVal Target As Range) 'MsgBox "You just changed " & Target.Address Call Module1.AutoFitMergedCellRowHeight End Sub I effective want a cell to automatically resize itself after the cell value has changed. The manually triggered resize works fine but the auto resize does not. Any insight would be appreciated. EricLCTCS |
#3
|
|||
|
|||
That handles the resizing perfectly... Thank you. However, I've run into another issue you might have some insight on. Once this event fires it appears to change the cell lock status from "unchecked" to a "square" (I have no idea what this represents) and now can't change that field anymore.
Since I don't want users to be messing with the spreadsheet format I have the sheet protected with only unlocked cells being available for edit. Once one of the editable cells fires this event it's locked status is being changed. Do you know what a square symbol means in this indicator? Any idea why it's changing from unchecked to the square? How might I get around this? Thanks for all your help. Eric ------------------------------------------------------------------------- Quote:
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling module procedure from sheet procedure
I don't know what "square symbol" you are referring to.
You can run the code on a protected sheet. Amended code . . . . . . . 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 With Target If .MergeCells And .WrapText Then ActiveSheet.Unprotect Password:="justme" 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 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 ActiveSheet.Protect Password:="justme" Application.ScreenUpdating = True End If End With End Sub Gord On Fri, 20 Jun 2014 14:20:52 +0100, EricLCTCS wrote: That handles the resizing perfectly... Thank you. However, I've run into another issue you might have some insight on. Once this event fires it appears to change the cell lock status from "unchecked" to a "square" (I have no idea what this represents) and now can't change that field anymore. Since I don't want users to be messing with the spreadsheet format I have the sheet protected with only unlocked cells being available for edit. Once one of the editable cells fires this event it's locked status is being changed. Do you know what a square symbol means in this indicator? Any idea why it's changing from unchecked to the square? How might I get around this? Thanks for all your help. Eric ------------------------------------------------------------------------- 'Gord Dibben[_2_ Wrote: ;1617903']This Greg Wilson worksheet event code does the trick. Just make sure merged cells are pre-formatted to wrap text. 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 With Target If .MergeCells And .WrapText Then 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 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 Application.ScreenUpdating = True End If End With End Sub Gord On Thu, 19 Jun 2014 17:23:29 +0100, EricLCTCS wrote: - Newbie non developer here... I have created a procedure "AutoFitMergedCellRowHeight()" in Module1 that I can manually run against a field in a spreadsheet that that successfully resizes a cell height depending on the amount of text entered: Sub AutoFitMergedCellRowHeight() Dim CurrentRowHeight As Single, MergedCellRgWidth As Single Dim CurrCell As Range, RangeWidth As Single Dim ActiveCellWidth As Single, PossNewRowHeight As Single If ActiveCell.MergeCells Then With ActiveCell.MergeArea If .Rows.Count = 1 And .WrapText = True Then Application.ScreenUpdating = False CurrentRowHeight = .RowHeight ActiveCellWidth = ActiveCell.ColumnWidth RangeWidth = .Width For Each CurrCell In Selection MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth Next MergeCells = False Cells(1).ColumnWidth = MergedCellRgWidth While .Cells(1).Width < RangeWidth Cells(1).ColumnWidth = .Cells(1).ColumnWidth + 0.5 Wend Cells(1).ColumnWidth = .Cells(1).ColumnWidth - 0.5 EntireRow.AutoFit PossNewRowHeight = .RowHeight Cells(1).ColumnWidth = ActiveCellWidth MergeCells = True RowHeight = IIf(CurrentRowHeight PossNewRowHeight, _ CurrentRowHeight, PossNewRowHeight) End If End With End If End Sub I have also created a procedure under Sheet4: Sub Worksheet_Change(ByVal Target As Range) 'MsgBox "You just changed " & Target.Address Call Module1.AutoFitMergedCellRowHeight End Sub I effective want a cell to automatically resize itself after the cell value has changed. The manually triggered resize works fine but the auto resize does not. Any insight would be appreciated. EricLCTCS- |
#5
|
|||
|
|||
Let me try to rephrase... When the re-size procedure is triggered it appears to be turning on the locked indicator in one of the cells that make up the total merged cell (maybe that's why it's displaying a square instead of the check on the lock indicator.
As I understand it, in order for user to be limited to enter data into only certain cells you set the locked indicator to on for those cells and set it to off of the cells you want editable. When protecting the sheet I am only allowing modification to unlocked cells. What appears to be happening when the procedure triggers is that it re-sizes the merged cells and then turns on the lock indicator in one of the cells that make up the merged cell, thus locking the entire merged cell to further modification once the procedure re-enabled the sheet protection. Does that make sense? |
#6
|
|||
|
|||
Maybe the better question is what code needs to be added to your last code to explicitly unlock the cell range you just re-sized?
Wouldn't take clear the issue up? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling module procedure from sheet procedure
Not sure what's going on. I cannot replicate.
Do you set unlocked property on idividual cells then merge them into one? Does it make a difference if you set the unlocked property on the merged cells post merging? Gord On Fri, 20 Jun 2014 17:31:36 +0100, EricLCTCS wrote: Let me try to rephrase... When the re-size procedure is triggered it appears to be turning on the locked indicator in one of the cells that make up the total merged cell (maybe that's why it's displaying a square instead of the check on the lock indicator. As I understand it, in order for user to be limited to enter data into only certain cells you set the locked indicator to on for those cells and set it to off of the cells you want editable. When protecting the sheet I am only allowing modification to unlocked cells. What appears to be happening when the procedure triggers is that it re-sizes the merged cells and then turns on the lock indicator in one of the cells that make up the merged cell, thus locking the entire merged cell to further modification once the procedure re-enabled the sheet protection. Does that make sense? |
#8
|
|||
|
|||
I un-merged the cell, set all the cells to unlocked, and then re-merged them. When I the modify the cell the procedure triggers and re-formats the merged cell to multi-line.
If I then view the properties of the cell on the protection tab the "locked" box has a square in it (not cleared, not checked). When the sheet protection is put back on I can not select that cell any more (I'm assuming because of the square status in the Locked checkbox. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling module procedure from sheet procedure
I un-merged the cell, set all the cells to unlocked, and then
re-merged them. When I the modify the cell the procedure triggers and re-formats the merged cell to multi-line. Normally, if the upper left cell of a group is not locked and that group gets merged, then all cells in the group become not locked. They remain not locked when the group is unmerged. If I then view the properties of the cell on the protection tab the "locked" box has a square in it (not cleared, not checked). Not able to duplicate this! Perhaps your workbook is corrupt... When the sheet protection is put back on I can not select that cell any more (I'm assuming because of the square status in the Locked checkbox. FWIW I normally 'reset' protection when a workbook with protected sheets is opened so code can modify locked cells without having to toggle protection off/on. This is possible by setting the parameter 'UserInterfaceOnly=True', which doesn't persist between sessions. This combines the best of both features in that users can modify unlocked cells and code can modify all cells. Merged cells are typically problematic for code anyway, and so should be avoided. Not always possible when designing forms and so we have to use merged cell areas. Regardless, your problem seems to be unique to the file you're working on and so I recommend rebuilding the project in a new file! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling module procedure from sheet procedure
First crtl-click or shift-click to select just the cells you want to
be unlocked. Then goto FormatCellsProtection and clear the "Locked" option. Before you protect the sheet look at the settings under ToolsProtectionProtect Sheet. Make sure you have enabled "select unlocked cells" Gord On Fri, 20 Jun 2014 21:19:15 +0100, EricLCTCS wrote: I un-merged the cell, set all the cells to unlocked, and then re-merged them. When I the modify the cell the procedure triggers and re-formats the merged cell to multi-line. If I then view the properties of the cell on the protection tab the "locked" box has a square in it (not cleared, not checked). When the sheet protection is put back on I can not select that cell any more (I'm assuming because of the square status in the Locked checkbox. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW TO CARRY A VAIABLE RESULTS FROM EXCEL SHEET PROCEDURE TO A MODULE | Excel Discussion (Misc queries) | |||
How to jump from a Form procedure to a Workbook or Module procedure? | Excel Programming | |||
Calling an embedded items event procedure from within a normal module | Excel Programming | |||
Calling a procedure in a procedure | Excel Programming | |||
Calling a procedure in a procedure | Excel Programming |