VBA Help!

[Pdj]

Hello all,

I am slowly learning how to use VBA but I could use some help with my current project. I need a VBA code to insert a text box next to the active cell. I found the code below online but I am not sure how to edit the text box size and border color. Any assistance is greatly appreciated.

Option Explicit
Sub PrP_WKP()

 Dim mycell As Range
 Dim myTextBox As TextBox

 Set mycell = ActiveCell

 With mycell
 Set myTextBox = .Parent.TextBoxes.Add(Top:=.Top, Left:=.Left, _
 Width:=.Width, Height:=.Height)

 With myTextBox
 .Caption = "A" 'if you want...
 End With
 
 End With

 End Sub

 

[sericom]

 With myTextBox
    .Caption = "A" 'if you want...
    .Border.Color = vbBlack
    .Border.Weight = xlMedium
    .Width = 100
    .Height = 20
 End With

 

[JLGWhiz]

this would add your text box and anchor it into the cell adjacent to the active cell with the top left corner of the TB in the top left corner of the cell. The 150 is the width of the TB in points and the 50 is the height of the Tb in points. You can play with that to get your dimensions where you want them.

Sub addTextBox()
ActiveSheet.Shapes.addTextBox msoTextOrientationHorizontal, ActiveCell.Offset(, 1).Left, ActiveCell.Offset(, 1).Top, 150, 50
End Sub

 

[JLGWhiz]

Some additional information:
the Top parameter refers to the position measured from the top of your display monitor, which is set by your screen resolution and determines how great a distance will be available in terms of pixels, points or inches. The default measurement is in points and don't ask me how to convert it to either of the other two because I never bothered learning that. I just throw some numbers in the parameter slot and work from there. The Left parameter is measured from the Left side of your screen. So, If you want your TB to appear in the upper left corner you could use Left:=10, Top:=10 and it would anchor slightly off the upper left corner of your screen. Say your screen resolution is set to 1240x820 and you want your TB to appear in the lower right corner of your screen, you would use something in the neighborhood of Left:=1100, Top:=750. But in your case, you want it beside the ActiveCell, so Just use the Offset method as in my previous post, then use the Cell to tell Excel where to put the anchor, since it already knows the grid location of the cell. The cell's Left and Top are translated by VBA into a grid location and if you want to know what that location is in real numbers, you can insert message box like:

MsgBox ActiveCell.Offset(, 1).Top & "  " & ActiveCell.Offset(, 1).Left

that will give you the actual number of points from the top and left of the screen. I spent several hours learning to use this information and still have problems with it periodically. So don't feel bad if you don't get it right the first time.
You can also use the message box to get your max width and max height.

MsgBox ActiveWindow.UsableWidth & "  " & ActiveWindow.UsableHeight

Remember it is measuring from the top of your screen, not necessarily the top of your sheet. So for anchoring controls, it is best to always use a cell reference somewhere on the worksheet.

 

[Pdj]

Thank you both!! I am playing with the positioning of the TB now, your explanation was great. The sizing and line color worked wonderfully. If I may ask one additional question. Is there a way to adjust the font size so that when I type in the box it is already set?

 

[Pdj]

Thank you both!! I am playing with the positioning of the TB now, your explanation was great. The sizing and line color worked wonderfully. If I may ask one additional question. Is there a way to adjust the font size so that when I type in the box it is already set?

Thanks again for the help. I figured out the font size

With myTextBox
    .Border.Color = vbRed
    .Border.Weight = xlMedium
    .Width = 20
    .Height = 20
    .Font.Size = 8
    .Caption = "A" 'if you want...
 End With

Look ma' I did it!

 

[JLGWhiz]

Thanks again for the help. I figured out the font size

With myTextBox
    .Border.Color = vbRed
    .Border.Weight = xlMedium
    .Width = 20
    .Height = 20
    .Font.Size = 8
    .Caption = "A" 'if you want...
 End With

Look ma' I did it!

Trial and error are two good instructors,
Regards, JLG