I made a module to help me in job life. I am taking notes to much in a working
day in pc or in notebook but at the end i realize there are to much notes or
*.txt files and i confused a little and can't find what i am looking for.
I decided to use Excel's Cells as(like) *.txt Notepad files. I'm just using
one *.xls file and I'm taking all my notes there.
To reach the data what i need easily I added to SelectionChange Event a Code,
which resize the selected cell to the optimum size automatically to read the
data in it easily.
The Worksheet's Name is "NotePad" and VBA name is "NotePad_Wsht"
In Worksheet's Code Window, the code is as fallows:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
NotePad_Wsht_SelectionChange_Run
End Sub
And in the NotePad_Wsht_Module Module Window, the code is as fallows:
Option Explicit
Dim NPad As Worksheet '
Dim NPadRow As Integer
Dim NPadCol As Integer
Dim FirstTimeStarted_NotePad_Wsht_SelectionChange_Run As Boolean
'
Dim Set_NotePad_Wsht_ActiveCell_FullVisible_VersionNumber As Integer
Private Const KConstant = 5.52689756816507
Private Const CHRConstant = 1.5
Public Sub NotePad_Wsht_SelectionChange_Run()
NotePad_Wsht_SelectionChange_Run_01
End Sub
Private Sub NotePad_Wsht_SelectionChange_Run_01()
If Not FirstTimeStarted_NotePad_Wsht_SelectionChange_Run Then _
FirstTimeStarted_NotePad_Wsht_SelectionChange_Run_Sub
Set_NotePad_Wsht_ActiveCell_FullVisible_VersionNumber = 3
Set_NotePad_Wsht_Cell_DefaultSize
Get_NotePad_Wsht_ActiveCell_RowAndColumn
Set_NotePad_Wsht_ActiveCell_FullVisible
End Sub
Private Sub FirstTimeStarted_NotePad_Wsht_SelectionChange_Run_Sub()
FirstTimeStarted_NotePad_Wsht_SelectionChange_Run = True
NotePad_Wsht_Module_GeneralDeclarations
End Sub
Private Sub NotePad_Wsht_Module_GeneralDeclarations()
NotePad_Wsht_Module_GeneralDeclarations_Set_WorkSheetNames_Abbrv
End Sub
Private Sub NotePad_Wsht_Module_GeneralDeclarations_Set_WorkSheetNames_Abbrv()
Set NPad = NotePad_Wsht
End Sub
Private Sub Set_NotePad_Wsht_Cell_DefaultSize()
If NPadRow = 0 Then
NPad.Cells.ColumnWidth = 8.43
NPad.Cells.RowHeight = 12.75
Else
If Not NPad.Cells(NPadRow, NPadCol).ColumnWidth = 8.43 Then _
NPad.Cells(NPadRow, NPadCol).ColumnWidth = 8.43
If Not NPad.Cells(NPadRow, NPadCol).RowHeight = 12.75 Then _
NPad.Cells(NPadRow, NPadCol).RowHeight = 12.75
End If
End Sub
Private Sub Get_NotePad_Wsht_ActiveCell_RowAndColumn()
NPadRow = ActiveCell.Row
NPadCol = ActiveCell.Column
End Sub
Private Sub Set_NotePad_Wsht_ActiveCell_FullVisible()
Select Case Set_NotePad_Wsht_ActiveCell_FullVisible_VersionNumber
Case 3: Set_NotePad_Wsht_ActiveCell_FullVisible_Version3
End Select
End Sub
Private Sub Set_NotePad_Wsht_ActiveCell_FullVisible_Version3()
Dim ActiveCellLengthText
Dim RootKConstant
Dim BiggestCHRTall
Dim BiggestCHRArea
Dim AllAreaNeeded
Dim RootAreaNeeded
Dim StandartColumnWidth
Dim HowManyLines
Dim ColumnWidthE
Dim ColumnWidthN
Dim ColumnWidthA
Dim RowHeightA
If NotePad_Wsht.Cells(NPadRow, NPadCol) = "" Then Exit Sub
ActiveCellLengthText = Len(ActiveCell)
If ActiveCellLengthText < 7 Then Exit Sub
RootKConstant = KConstant ^ (0.5)
BiggestCHRTall = CHRConstant * KConstant
BiggestCHRArea = BiggestCHRTall * 12.75
AllAreaNeeded = BiggestCHRArea * ActiveCellLengthText
RootAreaNeeded = AllAreaNeeded ^ (0.5)
StandartColumnWidth = RootKConstant * 8.43
If RootAreaNeeded < StandartColumnWidth Then _
RootAreaNeeded = StandartColumnWidth
HowManyLines = RootAreaNeeded \ 12.75
If HowManyLines < 1 Then HowManyLines = 1
ColumnWidthE = RootAreaNeeded
ColumnWidthN = ColumnWidthE / KConstant
ColumnWidthA = ColumnWidthN + 0.29
RowHeightA = HowManyLines * 12.75
If ColumnWidthA > 255 Then ColumnWidthA = 255
ActiveCell.ColumnWidth = ColumnWidthA
If RowHeightA > 409 Then RowHeightA = 409
ActiveCell.RowHeight = RowHeightA
ActiveCell.WrapText = True
ActiveCell.HorizontalAlignment = xlCenter
ActiveCell.VerticalAlignment = xlCenter
End Sub
Note:There were lots of Comment Texts but i realized that the comments makes
the code harder to understand. So i deleted them.
Before I ask some questions about the code I want to tell you you can copy the
code and try safely. It works without problem. But still I have some questions
in my mind.
1.I want to Export this macro and import to any other xls file which i want.
But as you know, the code doesn't work alone. Code needs a starter in
SelectionChange Event. My question is; Can i add the SelectionChange Code by
programming VBA. I mean, when i import this module to an xls file, there will be
a Sub like "Engage" and if user run Engage Sub then Macro will add a
Worksheet("NotePad") and will add SelectionChange Code automatically. Is it
possible? Can we change the code by another code.
2.Actually this module is not latest version. The newer version is under
construction and there are lots of bugs in it. My second question is about
Comment Text.
I want to save all the datas about the cell's text in Comment Text. For
example in Comment Text it will a text like this:
"Cell Infýrmation:
First Entry :??.??.???? ??:??
Last Entry :??.??.???? ??:??
Modified :??.??.???? ??:??"
if the cell's data changed after cell's selected then Sub will replace
"??.??.?? ??:??" with Now(09.02.2007 20:55)[for example]. But there is problem
here. I don't know why. The code beow is not working:
ActiveCell.Comment.Text Text:=Now Start:=100 Overwrite:=true
if I change Overwrite.=false then it's working but the result is not what i
want:
"Cell Infýrmation:
First Entry :??.??.???? ??:??
Last Entry :??.??.???? ??:??
Modified :09.02.2007 20:55??.??.???? ??:??"
I have t use Comment Text because I need to let sub know what is the data in
the Cell is about. I mean, comment text will save lots of specialities about the
text in the cell. for example comment text will categorize the text in the cell
like(job,social,home,private notes etc.). So, I should solve this problem
3.Third question is overall question. Do you think that this Module is
logical. I mean if you would need something like what i need, would you use
Excel VBA or something else? Or maybe would ignore this and just use some paper
and pen? I really want to know your opinions.