Link textbox to comment of a cell

[renraw9002]

Is there a way to have a textbox linked to the comment box of a cell?

Idk how better to explain this. I have a textbox that always goes to column 58 for quantity of product removed at a specific step in the process. However there's several reasons why the product might be removed at that point. I'd like to add another textbox that if filled out will add a comment to that cell saying why it was removed. It won't always be filled out because we don't always throw out product at this point.

 

[dmt32]

Hi

Without more detail can only give general suggestion to do what you want which may just be some additional lines of code required to post the comment to the appropriate cell.

Something like this maybe

With Worksheets(1)
    
        'your code that posts data to range
    
            'ADD COMMENTS
            If Len(txtComments) > 0 Then
                With .Cells(RecordRow, 58)
                  If Not .Comment Is Nothing Then .Comment.Delete
                    .AddComment txtComments
                End With
            End If
            
    End With

Where txtComments is your comments textbox which I assume is on a userform?

If this is not what you need then suggest that you share the code that posts data to the range with the forum.

Dave

 

[renraw9002]

I can't use my work computer to get on this site so please bear with me. I'm just writing 1 line of code for 1 textbox cause I can't copy and paste everything. I just started even learning excel back in March so then getting into vba coding is way out of my comfort zone so please forgive my probably unorganized code. I'm doing my best.

Dim batch number as string
Batch_number = Trim(HarvestBatchSearch.Text)
Lastrow=worksheets("per batch data").cells(row.count, 2).end(xlUp).row

UnProtectSheet 'unprotects sheet

If msgbox("do you want to save the data?", vbYesNo+vbQuestion,"question")=vbNo then
Exit sub

End If

For I=2 to last row
If worksheets("per batch data").cells(I,2).value=Batch_Number then
Worksheets("Per Batch Data").cells(I,72).value=HarvRemBeforeAsp.text

Here the number written into harvest Batch search matches to the number in column B and then harvrembeforeasp goes to column 72. However there's a few reasons we might remove things at this step, but we don't usually remove anything unless something is wrong. Can I add another textbox where if I write something into that textbox it will write something into the comment box for the correct cell in column 72?

 

[dmt32]

I can't use my work computer to get on this site so please bear with me. I'm just writing 1 line of code for 1 textbox cause I can't copy and paste everything.

Understood but must appreciate can on make best guesses in with any suggestion.

Here the number written into harvest Batch search matches to the number in column B and then harvrembeforeasp goes to column 72. However there's a few reasons we might remove things at this step, but we don't usually remove anything unless something is wrong. Can I add another textbox where if I write something into that textbox it will write something into the comment box for the correct cell in column 72?

for example in a search code you would include add comments code I suggested in #post 2 something like this

Private Sub CommandButton1_Click()
    Dim wsPerBatchData  As Worksheet
    Dim BatchComments   As String
    Dim Batch_Number    As Variant, m As Variant
    
    Const MyPassword As String = "mypasswordhere"
    
    Set wsPerBatchData = ThisWorkbook.Worksheets("per batch data")
    
    Batch_Number = Trim(Me.HarvestBatchSearch.Text)
    If Len(Batch_Number) = 0 Then Exit Sub
    
    BatchComments = Me.txtComments.Text
    
    m = Application.Match(Val(Batch_Number), wsPerBatchData.Columns(2), 0)
    
    If Not IsError(m) Then
        If MsgBox("do you want To save the data?", 36, "question") = vbNo Then Exit Sub
        
        With wsPerBatchData
            .Unprotect MyPassword
            With .Cells(CLng(m), 72)
                .Value = Me.harvrembeforeasp.Value
                'ADD COMMENTS
                If Len(BatchComments) > 0 Then
                    If Not .Comment Is Nothing Then .Comment.Delete
                    .AddComment BatchComments
                End If
            End With
            .Protect MyPassword
        End With
        
    Else
        
        MsgBox Batch_Number & Chr(10) & "Record Not Found", 48, "Not Found"
        
    End If
End Sub

Note I have assumed that the value you are searching for in Column B is a number & this is just an untested example way you could approach your requirement - you will need to adapt as required to meet specific project need.

Hope Helpful but if need further assistance, post back & if possible provide copy of your code(s)

Dave

 

[renraw9002]

Private Sub HarvestSave_Click()
'Checks to make sure all Textboxes have values entered first
    If HarvestStartTime.Text = "" Then
        MsgBox "Please enter a Harvest Start Time, When Mills Removed From Incubator"
        Exit Sub
    End If
    If HarvestAdjStartTime.Text = "" Then
        MsgBox "Please enter a Harvest Adjuvant Addition Start Time"
        Exit Sub
    End If
    If HarvestAdjEndTime.Text = "" Then
        MsgBox "Please enter a Harvest Adjuvant Addition End Time"
        Exit Sub
    End If
    If HarvRemBeforeAsp.Text = "" Then
        MsgBox "Please enter a Mills Removed Before Aspiration Value"
        Exit Sub
    End If
    If NumberMillsHarvest.Text = "" Then
        MsgBox "Please enter a Mills Harvested Value"
        Exit Sub
    End If
    If NumberMathRemove.Text = "" Then
        MsgBox "Please enter a Mills Removed After Math Value"
        Exit Sub
    End If
    If NumberMillsUsed.Text = "" Then
        MsgBox "Please enter a Number of Mills used for Process Value"
        Exit Sub
    End If
    If LostRecal.Text = "" Then
        MsgBox "Please enter a How Many Mills Removed due to Recalculation Value"
        Exit Sub
    End If
    If AvgWetWt.Text = "" Then
        MsgBox "Please enter a Average Wet Weight Value"
        Exit Sub
    End If
    If HarvestYield.Text = "" Then
        MsgBox "Please enter a Harvest Yield Value"
        Exit Sub
    End If

    Dim Batch_Number As String
        Batch_Number = Trim(HarvestBatchSearch.Text)
        lastrow = Worksheets("Per Batch Data").Cells(Rows.Count, 2).End(xlUp).Row
    
    UnProtectSheet 'runs macro to un-protect sheet before entering information
    
    'Asks if user is sure they want to save.
    If MsgBox("Do you want to save the data?", vbYesNo + vbQuestion, "question") = vbNo Then
        Exit Sub
        
    End If
    
    'Will not save over start and end dates or Incubator IDs if someone tries to edit them
    For I = 2 To lastrow
        If Worksheets("Per Batch Data").Cells(I, 2).Text = Batch_Number Then
            Worksheets("Per Batch Data").Cells(I, 64).Text = HarvestStartTime.Text
            Worksheets("Per Batch Data").Cells(I, 70).Text = HarvestAdjStartTime.Text
            Worksheets("Per Batch Data").Cells(I, 71).Text = HarvestAdjEndTime.Text
            Worksheets("Per Batch Data").Cells(I, 72).Value = HarvRemBeforeAsp.Value
            Worksheets("Per Batch Data").Cells(I, 73).Value = NumberMillsHarvest.Value
            Worksheets("Per Batch Data").Cells(I, 74).Value = NumberMathRemove.Value
            Worksheets("Per Batch Data").Cells(I, 75).Value = NumberMillsUsed.Value
            Worksheets("Per Batch Data").Cells(I, 78).Value = LostRecal.Value
            Worksheets("Per Batch Data").Cells(I, 76).Value = AvgWetWt.Value
            Worksheets("Per Batch Data").Cells(I, 80).Value = HarvestYield.Value
            Worksheets("Per Batch Data").Cells(I, 202).Text = HarvNotes.Text
        End If
    Next
    
    'reprotects the sheet after answering form and clears out form for next batch
    ReProtectSheet
    'blanks textboxes to reset form for next entry
    Initiatize_Inputs
    
End Sub

Sorry it's been awhile and my work computer didn't like this website for the longest time, but I think I'm good now. I'm not sure how much this helps with what I was trying to explain before. You can't see any batch numbers that get searched in Column B in my attached photo, but that's the column harvbatchsearch looks through and finds the row with the matching batch number. Then harvstarttime, etc all go to the correct column for that row and plug in the info from my vba form. I had to keep the format of column B as General because that column also has some Xlookup formulas on it for other information. It's Harvrembeforeasp, column 72 that gets the quantity removed, but if I add another textbox to my form how can I get that to go into the comment box for column 72 of the correct row?

 

[dmt32]

It's Harvrembeforeasp, column 72 that gets the quantity removed, but if I add another textbox to my form how can I get that to go into the comment box for column 72 of the correct row?

The sample suggestion I posted should do what you want

assuming that your textbox is named BatchComments trying updating your existing code that posts to the ranges as follows

With ThisWorkbook.Worksheets("Per Batch Data")
        'Will not save over start and end dates or Incubator IDs if someone tries to edit them
        For I = 2 To lastrow
            If .Cells(I, 2).Text = Batch_Number Then
                .Cells(I, 64).Text = HarvestStartTime.Text
                .Cells(I, 70).Text = HarvestAdjStartTime.Text
                .Cells(I, 71).Text = HarvestAdjEndTime.Text
                
                With .Cells(I, 72)
                    .Value = HarvRemBeforeAsp.Value
                    If Len(BatchComments) > 0 Then
                        If Not .Comment Is Nothing Then .Comment.Delete
                        .AddComment BatchComments
                    End If
                End With
              
                .Cells(I, 73).Value = NumberMillsHarvest.Value
                .Cells(I, 74).Value = NumberMathRemove.Value
                .Cells(I, 75).Value = NumberMillsUsed.Value
                .Cells(I, 78).Value = LostRecal.Value
                .Cells(I, 76).Value = AvgWetWt.Value
                .Cells(I, 80).Value = HarvestYield.Value
                .Cells(I, 202).Text = HarvNotes.Text
               Exit For
            End If
    Next
End With

'Rest of code

Solution untested & always make backup before testing new code
If still unsure then if possible, make a copy of your workbook with DUMMY data & place in a file sharing site like Dropbox & provide a link to it

Dave

 

[renraw9002]

With Worksheets("Per Batch Data").Cells(I, 72).Value = HarvRemBeforeAsp.Value
                If Len(RemBefAspNotes) > 0 Then
                    If Not RemBefAspNotes Is Nothing Then .Comment.Delete
                    .AddComment RemBefAspNotes
                End If
            End With

it says the .comment.delete part needs an object?

 

[dmt32]

With Worksheets("Per Batch Data").Cells(I, 72).Value = HarvRemBeforeAsp.Value
                If Len(RemBefAspNotes) > 0 Then
                    If Not RemBefAspNotes Is Nothing Then .Comment.Delete
                    .AddComment RemBefAspNotes
                End If
            End With

it says the .comment.delete part needs an object?

You have not followed suggestion I posted

I have updated it to include your actual textbox name

With ThisWorkbook.Worksheets("Per Batch Data")
        'Will not save over start and end dates or Incubator IDs if someone tries to edit them
        For I = 2 To lastrow
            If .Cells(I, 2).Text = Batch_Number Then
                .Cells(I, 64).Text = HarvestStartTime.Text
                .Cells(I, 70).Text = HarvestAdjStartTime.Text
                .Cells(I, 71).Text = HarvestAdjEndTime.Text
                
               
              With .Cells(I, 72)
                    .Value = HarvRemBeforeAsp.Value
                    If Len(RemBefAspNotes.Value) > 0 Then
                        If Not .Comment Is Nothing Then .Comment.Delete
                        .AddComment RemBefAspNotes.Value
                    End If
              End With
              
                .Cells(I, 73).Value = NumberMillsHarvest.Value
                .Cells(I, 74).Value = NumberMathRemove.Value
                .Cells(I, 75).Value = NumberMillsUsed.Value
                .Cells(I, 78).Value = LostRecal.Value
                .Cells(I, 76).Value = AvgWetWt.Value
                .Cells(I, 80).Value = HarvestYield.Value
                .Cells(I, 202).Text = HarvNotes.Text
               Exit For
            End If
    Next
End With

Dave