PDA

View Full Version : How to adjust the size of the `Note` after opening it?



rediffusion
08-07-2019, 08:32 AM
So here is macro:

Private Sub NoteZoom3()
With ActiveWindow.VisibleRange
NoteChangeSize .Width, .Height, True
End With
End Sub

Private Sub NoteChangeSize(w!, h!, Optional scr As Boolean)
With ActiveCell.Comment.Shape
.Width = w: .Height = h
If scr Then .Top = 0: .Left = 0: .Visible = msoTrue
End With
End Sub

How it works... go to the cell that contains `Note` > Run the macro!
`Note` is adjusted to the size Of the sheet window (we get <Full Screen>).
It's all bad if we for example are somewhere in the column (AB) or line 60. `Note` that opens in <Full Screen> but it is fixed in the top where the cell (A) and I need to scroll up to see.

I would like to be adjusted to the size of the Sheet and opened exactly where the `Note` (that each time not to climb up).

Artik
08-07-2019, 04:04 PM
Not everything is clear to me, but more or less something like this:
Private Sub NoteChangeSize(w As Single, h As Single, Optional scr As Boolean) Dim rngVR As Range

With ActiveCell
'Set the active cell in the upper left corner of the sheet
ActiveWindow.ScrollColumn = .Column
ActiveWindow.ScrollRow = .Row

With .Comment.Shape
.Width = w: .Height = h

If scr Then
Set rngVR = ActiveWindow.VisibleRange

.Top = rngVR.Top: .Left = rngVR.Left
End If

.Visible = msoTrue
End With

End With
End Sub
Artik

rediffusion
08-08-2019, 12:07 AM
Hi Artik !

Thank you Sr.
But no need scroll - Just open <Full Screen>.
I little bit correct the code. Now it's look better:


Private Sub NoteChangeSize(w!, h!, Optional scr As Boolean)
With ActiveCell.Comment.Shape
.Width = w: .Height = h
If scr Then .Top = ActiveWindow.VisibleRange.Top: .Left = ActiveWindow.VisibleRange.Left: .Visible = msoTrue
End With
End Sub

rediffusion
08-10-2019, 11:17 AM
Private Sub NoteZoom3()
With ActiveWindow.VisibleRange
NoteChangeSize .Width, .Height, True
End With
End Sub

Private Sub NoteChangeSize(w!, h!, Optional t! Optional l! Optional scr AsBoolean)
With ActiveCell.Comment.Shape
.Width = w: .Height = h
If scr Then .Top = 0: .Left = 0: .Visible = msoTrue
End With
End Sub