Add COMMENT to a formula ?

[Logit]

I'm trying to add a comment to a formula. Research shows the following will work :

=NOW()+N("This is my comment")

Place that formula into a cell and all that will show in the cell is the current date and time.

Now, I'm using a ComboBox control on a User Form, that will hold different formulas for selection which are subsequently written to a different worksheet.

I'd like to include a comment at the end of each formula so the user will understand which formula to choose.
Using the following syntax (without a comment) works for a ComboBox:

"=NOW()"

An error is produced if I use:

"=NOW() + N("This is my comment")"

What syntax will produce the result I am seeking ?

Thank you for your assistance.

[teylyn]

Why don't you show only the comment in the combobox and then use VBA to apply the respective formula?

[xlnitwit]

Hi,

You must escape the quotation marks contained within the string by doubling them so that the compiler knows you are not trying to terminate the string.

"=NOW() + N(""This is my comment"")"

[Winon]

Hello Logit,

Will this Code work for you?

Option Explicit
Private Sub ComboBox1_AfterUpdate()
Sheet2.Range("B4") = Me.ComboBox1.Text & "  " & TextBox1.Value
End Sub

Private Sub CommandButton3_Click()
    Unload Me
    If Sheet2.Cells(4, 2) <> "" Then
    Sheet2.Select
    Range("A4").Select
    Else: Exit Sub
    End If
End Sub

Private Sub UserForm_Activate()
    With ComboBox1
        .AddItem "TIME(1,0,0)"
        .AddItem "TIME(2,0,0)"
        .AddItem "TIME(3,0,0)"
        .AddItem "TIME(4,0,0)"
        .AddItem "TIME(5,0,0)"
        .AddItem "TIME(6,0,0)"
        .AddItem "TIME(7,0,0)"
        .AddItem "TIME(8,0,0)"
        .AddItem "TIME(9,0,0)"
        .AddItem "TIME(10,0,0)"
        .AddItem "TIME(11,0,0)"
        .AddItem "TIME(12,0,0)"
        .AddItem "TIME(13,0,0)"
        .AddItem "TIME(14,0,0)"
        .AddItem "TIME(15,0,0)"
        .AddItem "TIME(16,0,0)"
        .AddItem "TIME(17,0,0)"
        .AddItem "TIME(18,0,0)"
        .AddItem "TIME(19,0,0)"
        .AddItem "TIME(20,0,0)"
        .AddItem "TIME(21,0,0)"
        .AddItem "TIME(22,0,0)"
        .AddItem "TIME(23,0,0)"
    End With
End Sub

'Sub viewUserForm1()
'    UserForm1.Show
'End Sub

Private Sub CommandButton2_Click()

    clearForm

End Sub

Private Sub clearForm()
    'clear all fields in UserForm
     
    'TextBox to accept name
    TextBox1.Value = ""
    
    ComboBox1.Value = ""
    'CheckBox to select "Reading"

End Sub

Private Sub CommandButton1_Click()

    If ComboBox1.Value = "" Then
        MsgBox "Please Select A Time Zone", vbOKOnly, "Time Zone Error Alert!"
    ElseIf TextBox1.Text = "" Then
        MsgBox "Please Enter Your Call Letters", vbOKOnly, "Call Letters Error!"
    Else
        Sheet2.Cells(4, 2) = Me.ComboBox1.Text & "  " & TextBox1.Value
        Sheet2.Cells(50, 2) = ComboBox1.Value       'Why the value so far down below?
    
    'run the macro - Private Sub clearForm(), to clear all fields in UserForm
    clearForm
    End If
End Sub

[Logit]

Thank you all for your suggestions.

If the .AddItem selection is the first on the list, and it appears as below"

Private Sub UserForm_Activate()
    With ComboBox1
        .AddItem "=A1+TIME(1,0,0) +1 Hour GMT"
        .AddItem "=A1+TIME(2,0,0)"
        .AddItem "=A1+TIME(3,0,0)"

I need for it to write Sheet2.Cells(4, 2) =A1+TIME(1,0,0) and not write the remainder +1 Hour GMT

I've tried several variations of the LEFT command but not having success.

Again, thank you for your assistance !

[Logit]

Oh ... the length of text after = A1+TIME (1,0,0) will always be 12.

[Winon]

Hello Logit,

Is this Code below what you want?

Option Explicit

Private Sub CommandButton3_Click()
    Unload Me
    If Sheet2.Cells(4, 2) <> "" Then
    Sheet2.Select
    Range("A4").Select
    Else: Exit Sub
    End If
End Sub

Private Sub UserForm_Activate()
    With ComboBox1
        .AddItem "A1+TIME(1,0,0)"
        .AddItem "A1+TIME(2,0,0)"
        .AddItem "A1+TIME(3,0,0)"
        .AddItem "A1+TIME(4,0,0)"
        .AddItem "A1+TIME(5,0,0)"
        .AddItem "A1+TIME(6,0,0)"
        .AddItem "A1+TIME(7,0,0)"
        .AddItem "A1+TIME(8,0,0)"
        .AddItem "A1+TIME(9,0,0)"
        .AddItem "A1+TIME(10,0,0)"
        .AddItem "A1+TIME(11,0,0)"
        .AddItem "A1+TIME(12,0,0)"
        .AddItem "A1+TIME(13,0,0)"
        .AddItem "A1+TIME(14,0,0)"
        .AddItem "A1+TIME(15,0,0)"
        .AddItem "A1+TIME(16,0,0)"
        .AddItem "A1+TIME(17,0,0)"
        .AddItem "A1+TIME(18,0,0)"
        .AddItem "A1+TIME(19,0,0)"
        .AddItem "A1+TIME(20,0,0)"
        .AddItem "A1+TIME(21,0,0)"
        .AddItem "A1+TIME(22,0,0)"
        .AddItem "A1+TIME(23,0,0)"
    End With
End Sub

'Sub viewUserForm1()
'    UserForm1.Show
'End Sub

Private Sub CommandButton2_Click()

    clearForm

End Sub

Private Sub clearForm()
    'clear all fields in UserForm
     
    'TextBox to accept name
    TextBox1.Value = ""
    
    ComboBox1.Value = ""
    'CheckBox to select "Reading"

End Sub

Private Sub CommandButton1_Click()

    If ComboBox1.Value = "" Then
        MsgBox "Please Select A Time Zone", vbOKOnly, "TIME Zone Error Alert!"
    ElseIf TextBox1.Text = "" Then
        MsgBox "Please Enter Your Call Letters", vbOKOnly, "Call Letters Error!"
    Else
        Sheet2.Cells(4, 2) = Me.ComboBox1.Text & "  " & TextBox1.Value
        Sheet2.Cells(50, 2) = ComboBox1.Value       'Why the value so far down below?
    
    'run the macro - Private Sub clearForm(), to clear all fields in UserForm
    clearForm
    End If
End Sub

Regards.

[Logit]

Winon:

Thank you for your reply

Refer to the attached, Sheet #2.

[Winon]

Hi Logit,

Call Letters <---- Call Letters gets written to a different cell. Where?

Please try try the revised Code below;

Option Explicit
Private Sub CommandButton3_Click()
    Unload Me
End Sub

Private Sub UserForm_Activate()
    With ComboBox1
    
        .AddItem "A1+TIME(1,0,0) Your Zone +1 GMT"
        .AddItem "A1+TIME(2,0,0) Your Zone +2 GMT"
        .AddItem "A1+TIME(3,0,0) Your Zone +3 GMT"
        .AddItem "A1+TIME(4,0,0) Your Zone +4 GMT"
        .AddItem "A1+TIME(5,0,0) Your Zone +5 GMT"
        .AddItem "A1+TIME(6,0,0) Your Zone +6 GMT"
        .AddItem "A1+TIME(7,0,0) Your Zone +7 GMT"
        .AddItem "A1+TIME(8,0,0) Your Zone +8 GMT"
        .AddItem "A1+TIME(9,0,0) Your Zone +9 GMT"
        .AddItem "A1+TIME(10,0,0) Your Zone +10 GMT"
        .AddItem "A1+TIME(11,0,0) Your Zone +11 GMT"
        .AddItem "A1+TIME(12,0,0) Your Zone +12 GMT"
        .AddItem "A1+TIME(13,0,0) Your Zone +13 GMT"
        .AddItem "A1+TIME(14,0,0) Your Zone +14 GMT"
        .AddItem "A1+TIME(15,0,0) Your Zone +15 GMT"
        .AddItem "A1+TIME(16,0,0) Your Zone +16 GMT"
        .AddItem "A1+TIME(17,0,0) Your Zone +17 GMT)"
        .AddItem "A1+TIME(18,0,0) Your Zone +18 GMT"
        .AddItem "A1+TIME(19,0,0) Your Zone +19 GMT"
        .AddItem "A1+TIME(20,0,0) Your Zone +20 GMT"
        .AddItem "A1+TIME(21,0,0) Your Zone +21 GMT"
        .AddItem "A1+TIME(22,0,0) Your Zone +22 GMT"
        .AddItem "A1+TIME(23,0,0) Your Zone +23 GMT"
    
    End With
End Sub

'Sub viewUserForm1()
'    UserForm1.Show
'End Sub

Private Sub CommandButton2_Click()

    clearForm

End Sub

Private Sub clearForm()
    'clear all fields in UserForm
     
    'TextBox to accept name
    TextBox1.Value = ""
    
    ComboBox1.Value = ""
    'CheckBox to select "Reading"

End Sub

Private Sub CommandButton1_Click()

    If ComboBox1.Value = "" Then
        MsgBox "Please Select A Time Zone", vbOKOnly, "Time Zone Error Error!"
    ElseIf TextBox1.Text = "" Then
        MsgBox "Please Enter Your Call Letters", vbOKOnly, "Call Letters Error!"
    Else
        Sheet2.Cells(2, 400) = ComboBox1.Value
        Sheet2.Cells(6, 2).Formula = ("=SUBSTITUTE(OJ2,OJ2,LEFT(OJ2,15))")
        Sheet2.Cells(6, 2).Value = Sheet2.Cells(6, 2).Value
        Sheet2.Cells(2, 400).ClearContents
        Sheet2.Cells(4, 2) = TextBox1.Value
    
    'run the macro - Private Sub clearForm(), to clear all fields in UserForm
    clearForm
    End If
End Sub

[Logit]

Winon:

Please see the attached, page #2.

Thanks.

[Logit]

Winon:

Thank you for the reply. I am out of town for a day, will respond upon my return.

[protonLeah]

If you have a time in cell A1 (don't know which sheet, though) try:

Private Sub SaveButton_Click()
    If TimeZone.Value = "" Then
        MsgBox "Please Select A Time Zone", vbOKOnly, "Time Zone Error Error!"
    ElseIf CallLetters.Text = "" Then
        MsgBox "Please Enter Your Call Letters", vbOKOnly, "Call Letters Error!"
    Else
        With Sheet2
            .Cells(4, 2).Value = CallLetters.Value
            .Cells(6, 2).Value = TimeValue(.Range("A1").Text) + TimeValue(TimeZone.ListIndex + 1 & ":0:0")
            .Cells(6, 3).Value = Mid(TimeZone.Text, InStr(TimeZone.Text, "Your"))
        End With
    
        'run the macro - Private Sub clearForm(), to clear all fields in UserForm
        clearForm
    End If
End Sub

[Logit]

protonLeah:

I get an error message [ Run time error '13': Type Mismatch ] on this line:

Private Sub SaveButton_Click()
    If TimeZone.Value = "" Then
        MsgBox "Please Select A Time Zone", vbOKOnly, "Time Zone Error Error!"
    ElseIf CallLetters.Text = "" Then
        MsgBox "Please Enter Your Call Letters", vbOKOnly, "Call Letters Error!"
    Else
        With Sheet2
            .Cells(4, 2).Value = CallLetters.Value
            .Cells(6, 2).Value = TimeValue(.Range("A1").Text) + TimeValue(TimeZone.ListIndex + 1 & ":0:0")
            .Cells(6, 3).Value = Mid(TimeZone.Text, InStr(TimeZone.Text, "Your"))
        End With
    
        'run the macro - Private Sub clearForm(), to clear all fields in UserForm
        clearForm
    End If
End Sub

[mikerickson]

You can include a comment in a cells formula with

=IF(FALSE, someFormula, "this is my comment")

[Winon]

Hi Logit,

Please try the attached revised Workbook

Kind Regards.

[sanram]

May be you are trying to do this.

=NOW()+N("This is a comment")

See the attachment.

[sanram]

This is a trick, nothing else. But this trick will work with the numerical data only. Because N("Any String") returns 0 and if you add 0 with anything then the result will be same. But if you use that with a string value then it will show a 0 at the right of your result. Say, A1=Rose, then if you use

=A1+N("Comment")

Then the result will be Rose0

I hope that it makes sense.

[Winon]

Hi sanram, thank you for you input.

I have already tried and tired that approach, but it does not entertain the exact requirement of this Thread. Therefore I have uploaded my latest Workbook as a possible solution. Let us see what the outcome is.

Kind Regards.

[Logit]

Winon

How do I change:

Sheet2.Cells(4, 2).Formula = ("=SUBSTITUTE(OJ2,OJ2,LEFT(OJ2,15))")

so the printed result includes all of the following : =A1+TIME(1,0,0)


... so it prints the EQUAL sign also ...

[Logit]

Winon:

I'm studying your code

Sheet2.Cells(2, 400) = ComboBox1.Value
Sheet2.Cells(4, 2).Formula = ("=SUBSTITUTE(OJ2,OJ2,LEFT(OJ2,15))")
Sheet2.Cells(4, 2).Value = Sheet2.Cells(4, 2).Value
Sheet2.Cells(2, 400).ClearContents
Sheet2.Cells(6, 2) = TextBox1.Value

I understand ("=SUBSTITUTE(OJ2,OJ2,LEFT(OJ2,15))") is doing the work of extracting what is needed from the
text string - however - I don't understand what OJ2 is. I suspect OJ2 refers to the text string because in the last part of the formula is
LEFT(OJ2,15) which is what we are trying to extract ... the first 15 characters on the left side.

Still ... what is OJ2?

Also, why write the ComboBox. Value to a temporary location of B400 ? Unless something in the formula is using that information ?
I don't understand what part of the formula is referring to B400.

[protonLeah]

In post #1, you said:
"... Place that formula into a cell and all that will show in the cell is the current date and time..."
So I assumed that the A1 in your code was referring to a time in cell A1 on either sheet1 or sheet2 ( I didn't know which). In the workbook I attached, the code is looking for a time in Sheet2, A1. If it is blank the code (as written) will error. If there is a time, then the output is a time adjusted by the GMT selected from the combobox.

However, based on re-reading #1 I have modified the code to include the date. But I still don't know which Sheet A1 refers to so I have included both in the code. Comment out/delete as desired:

Private Sub SaveButton_Click()
    If TimeZone.Value = "" Then
        MsgBox "Please Select A Time Zone", vbOKOnly, "Time Zone Error Error!"
    ElseIf CallLetters.Text = "" Then
        MsgBox "Please Enter Your Call Letters", vbOKOnly, "Call Letters Error!"
    Else
        With Sheet2
            .Cells(4, 2).Value = CallLetters.Value
            
            'if time is on sheet 2
            .Cells(6, 2).Value = .Range("A1").Value + TimeValue(TimeZone.ListIndex + 1 & ":0:0")
            
            'if time is on sheet 1
            '.Cells(6, 2).Value = Sheets("sheet1").Range("A1").Value + TimeValue(TimeZone.ListIndex + 1 & ":0:0")
            
            'just a test comment line
            .Cells(6, 3).Value = Mid(TimeZone.Text, InStr(TimeZone.Text, "Your"))
        End With
    
        'run the macro - Private Sub clearForm(), to clear all fields in UserForm
        clearForm
    End If
End Sub

[Winon]

O.K. Logit,

I have decided to send you two sample Workbooks. One caters for =+TIME(3,0,0), and the other will give you =A1+TIME(1,0,0), so take your pick!

Should you still experience any issues, please feel free to advise accordingly.

Kind Regards.

[Logit]

protonLeah:

Thank you for your response.

From the code for ListBox1:

Private Sub UserForm_Activate()
    With ComboBox1
    
        .AddItem "=A1+TIME(1,0,0) Your Zone +1 GMT"
        .AddItem "=A1+TIME(2,0,0) Your Zone +2 GMT"
        .AddItem "=A1+TIME(3,0,0) Your Zone +3 GMT"
        .AddItem "=A1+TIME(4,0,0) Your Zone +4 GMT"
        .AddItem "=A1+TIME(5,0,0) Your Zone +5 GMT"
        .AddItem "=A1+TIME(6,0,0) Your Zone +6 GMT"
        .AddItem "=A1+TIME(7,0,0) Your Zone +7 GMT"
        .AddItem "=A1+TIME(8,0,0) Your Zone +8 GMT"
        .AddItem "=A1+TIME(9,0,0) Your Zone +9 GMT"
        .AddItem "=A1+TIME(10,0,0) Your Zone +10 GMT"
        .AddItem "=A1+TIME(11,0,0) Your Zone +11 GMT"
        .AddItem "=A1+TIME(12,0,0) Your Zone +12 GMT"
        .AddItem "=A1+TIME(13,0,0) Your Zone +13 GMT"
        .AddItem "=A1+TIME(14,0,0) Your Zone +14 GMT"
        .AddItem "=A1+TIME(15,0,0) Your Zone +15 GMT"
        .AddItem "=A1+TIME(16,0,0) Your Zone +16 GMT"
        .AddItem "=A1+TIME(17,0,0) Your Zone +17 GMT"
        .AddItem "=A1+TIME(18,0,0) Your Zone +18 GMT"
        .AddItem "=A1+TIME(19,0,0) Your Zone +19 GMT"
        .AddItem "=A1+TIME(20,0,0) Your Zone +20 GMT"
        .AddItem "=A1+TIME(21,0,0) Your Zone +21 GMT"
        .AddItem "=A1+TIME(22,0,0) Your Zone +22 GMT"
        .AddItem "=A1+TIME(23,0,0) Your Zone +23 GMT"
    
    End With
End Sub

(For the purpose of discussion I'm just selecting one item at random):

.AddItem "=A1+TIME(15,0,0) Your Zone +15 GMT"

... only =A1+TIME(15,0,0) needs to parsed from the line of text and written to a specific cell on sheet one.

The cell where it is written will refer to a time value in A1 of the same sheet and (because of the formula) display the current time
15 hours ahead of the time in cell A1.

Without the equal sign at the front of the parsed text, it won't be recognized as a formula.

Winon's last suggestion does everything needed but include the equal sign. I have attempted to modify the line of code:

Sheet2.Cells(4, 2).Formula = ("=SUBSTITUTE(OJ2,OJ2,LEFT(OJ2,15))")

without success. Although, this line
of code does specify the first 15 characters on the left side of the parsed text, I suspect VBA is thinking it needs to drop
the equal sign for some reason. I can't figure that part out.

A big thank you to all for your patience.

[Winon]

Hello Logit,

Thank you for your continued interest an intelligent observations.

OJ2 = Cells(2, 400), which i need to employ as a temporary helper column which is cleared after completion of the Code.

B400 = Me being tired and making a mistake. Please ignore B400.

With regards to your last request, i.e. -

...so the printed result includes all of the following : =A1+TIME(1,0,0)

In your last uploaded sheet however you state:

But all it needs to prints is: =+TIME(3,0,0)

It took me quite some time to get the latter working, but now I am confused. Could you please cofirm if you want =+TIME(3,0,0) or =A1+TIME(1,0,0)?

Thank you.

[Logit]

Winon:

YOU ARE THE MAN !!!!

Rev3 did it. Thank you so much.

There is alot for me to digest in the code. I find this self-study stimulating and rewarding. A great big THANK YOU to all who participated
in this thread. Your responses are greatly appreciated.

Time to move to the next segment of this project.

[Winon]

Hi Logit,

Thank you for the feedback, and also for adding to my Reputation, much appreciated!

You are welcome, glad I could help.

Good luck with your Project!

Regards.