need help to position excel comment with vba

[sumhungl0]

google is not helping on this problem. i have a sheet with IDs in the first 2 columns which are frozen so i can see the column while scrolling. the rest of the columns are dated at the top and this is where scheduled maintenance is input. this is a schedule for items due for 8 vehicles. column A has the vehicle IDs and column B is hiden but contains a status of the vehicle. what i am trying to do is leave column B hiden and input a comment in column A depending on what is in column B which is hiden. i have the comments working correctly except when scrolling to the right, you cannot see the comments anymore. if i could set the comment to position over its own cell it would work just fine. i cant seem to get any sample vba from googling to work. the position never changes and i cant figure it out. here is the code i have so far.

Dim sText As String ' Comment String
Dim i1 As Long ' Counter
Dim sUser As String ' User Name
sUser = Application.UserName

For i1 = 5 To 12
sText = ActiveSheet.Cells(i1, 2).Value
If sText = "Unserviceable" Then
'Deletes Existing Comments
Cells(i1, 1).ClearComments
' Creates Comment
Cells(i1, 1).AddComment
'Cells(i1, 1).Comment.Text Text:=sUser & Chr(10) & sText
Cells(i1, 1).Comment.Text Text:=sText
Cells(i1, 1).Comment.Shape.TextFrame.AutoSize = True
Else
Cells(i1, 1).ClearComments
End If
Next i1

does anyone know how to position the comment over the containing cell? thanks.

 

[JLGWhiz]

I don't believe position is one of the attributes of the comment object. It appears to be a fixed property in Excel. Have you looked at using Message Box or Labels to do what you want? Takes a bit of code, but is possible.

 

[sumhungl0]

i just want to be able to populate it from vba based on values in column B. ive never used message box or labels but if they show info when you mouse over like the comments do, that would be just fine. i will look into those, do you know how i can use them?

 

[JLGWhiz]

Not in the mouse over fashion.

 

[sumhungl0]

data validation is almost working for me. i tried to add it to the code above but it errors because the workbook is shared and for some reason it wont become exclusive to make the validation happen. here is what i have.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
'Transcribe Unserviceable
Dim wb As Workbook
    Application.ScreenUpdating = False ' turn off the screen updating
    Set wb = Workbooks.Open("C:\Users\me\Desktop\status.xlsm", True, True)
    ' open the source workbook, read only
    With ThisWorkbook.Worksheets("weekly")
        ' read data from the source workbook
        .Range("b5").Value = wb.Worksheets("status").Range("i5").Value
        .Range("b6").Value = wb.Worksheets("status").Range("i6").Value
        .Range("b7").Value = wb.Worksheets("status").Range("i7").Value
        .Range("b8").Value = wb.Worksheets("status").Range("i8").Value
        .Range("b9").Value = wb.Worksheets("status").Range("i9").Value
        .Range("b10").Value = wb.Worksheets("status").Range("i10").Value
        .Range("b11").Value = wb.Worksheets("status").Range("i11").Value
        .Range("b12").Value = wb.Worksheets("status").Range("i12").Value
    End With
    wb.Close False ' close the source workbook without saving any changes
    Set wb = Nothing ' free memory
    Application.ScreenUpdating = True ' turn on the screen updating
    
    'If ActiveWorkbook.MultiUserEditing Then
    'Application.DisplayAlerts = False
    'ActiveWorkbook.ExclusiveAccess 'not working
    'Application.DisplayAlerts = True
    'End If
'Insert Comments for Unserviceable
Dim sText As String ' Comment String
Dim i1 As Long ' Counter
Dim sUser As String ' User Name
sUser = Application.UserName
For i1 = 5 To 12
sText = ActiveSheet.Cells(i1, 2).Value
If sText = "Unserviceable" Then
'Deletes Existing Comments
Cells(i1, 1).ClearComments
' Creates Comment
Cells(i1, 1).AddComment
'Cells(i1, 1).Comment.Text Text:=sUser & Chr(10) & sText
Cells(i1, 1).Comment.Text Text:=sText
Cells(i1, 1).Comment.Shape.TextFrame.AutoSize = True
'Cells(i1, 1).Validation.Add = xlValidateTextLength
'Cells(i1, 1).Validation.InputMessage = sText
With Cells(i1, 1).Validation
        .Delete
        .Add Type:=xlValidateTextLength, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="2", _
            Formula2:="35"
        .IgnoreBlank = True
 .InputMessage = sText
End With

Else
Cells(i1, 1).ClearComments
Cells(i1, 1).Validation.Delete
End If
Next i1
    'If Not ActiveWorkbook.MultiUserEditing Then
    'Application.DisplayAlerts = False
    'ActiveWorkbook.MultiUserEditing 
    'ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, _
    '    accessMode:=xlShared
    'Application.DisplayAlerts = True
    'End If
End Sub

i commented out the code for exclusive and sharing because unless i manually unshare the workbook the code gives me error when adding validation. but it does work to show the info, you just have to click on the cell in column A. i think it has problems doing the validation.delete also, not sure why. and for some reason i cant get wildcards to work on my

If sText = "Unserviceable*" Then

anyone got any ideas? thanks.