Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    textbox=comment (Excel 2000)

    Hi all,

    I can't figure out why the object is not set, how do I pass along the value of the textbox to the comment.
    Anyone?


    Private Sub CommandButton3_Click()
    Dim stxtresponse As string
    stxtresponse = TextBox2.Value
    With Selection.Interior
    .ColorIndex = 2
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    End With
    On Error Resume Next
    ActiveCell.Comment.Delete
    On Error GoTo 0
    With Selection
    .AddComment
    .Comment.Visible = False
    .Comment.Text Text:="Unchecked at: " & VBA.Chr(10) _
    & VBA.Format(VBA.Now, "mmm/dd/yyyy hh:mm:ss") & "stxtreponse"
    End With
    FrmTSEV.Hide
    Unload FrmTSEV
    End Sub


    Thanks,

    Darryl.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: textbox=comment (Excel 2000)

    I see several possible problems.

    1- In the line that puts the text into the comment, the variable that contains the text is enclosed in quotes. Therefore, it is taken as a string, not a variable containing the string.

    2- In the same line, the variable name is mispelled (an s is missing). Since you don't have Option Explicit specified, the VBE will not tell you about this even after you remove the quote marks. It will just insert a null string from the variant variable that has never had a value assigned to it. This is why you should go to the VBE Tools/Options and set "Require variable definition" on the Editor tab.

    3- When you add a comment, the range must be a single cell. By using With Selection, you could have a multiple cell range if more than one cell is selected.

    Try the following:

    <pre>Option Explicit

    Private Sub CommandButton3_Click()
    Dim stxtresponse As String
    stxtresponse = TextBox2.Value
    With ActiveCell.Interior
    .ColorIndex = 2
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    End With
    On Error Resume Next
    ActiveCell.Comment.Delete
    On Error GoTo 0
    With ActiveCell
    .AddComment
    .Comment.Visible = False
    .Comment.Text Text:="Unchecked at: " & VBA.Chr(10) _
    & VBA.Format(VBA.Now, "mmm/dd/yyyy hh:mm:ss") & " " & stxtresponse
    End With
    FrmTSEV.Hide
    Unload FrmTSEV
    End Sub
    </pre>

    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: textbox=comment (Excel 2000)

    Thanks Legare,


    I appreciate the help, and your correcting my structure, frustration is not a good thing: <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>

    Darryl.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •