Member Avatar for dinilkarun

hi,

I want to put a textbox over a bitmap in MS Excel sheet and populate the textbox with a value from VB code. Please guide me in acheiving the same.


Regards,
Dinil

  • 2 Contributors
  • 3 Replies
  • 106 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by cguan_77

Dani AI

Generated

asked for a fully code-driven way to place a bitmap across a cell range and put a textbox on top; showed how to set a textbox value but did not show programmatic insertion/positioning. The reliable approach is to get the target Range coordinates (Left/Top/Width/Height), insert the picture sized to that rectangle, then add a drawing textbox positioned over it and populate the text.

Use the worksheet Range to drive placement, Shapes.AddPicture to insert the image, and Shapes.AddTextbox to create the overlaying label. Send the picture to the back and remove the textbox fill/line so the image shows through. See the Shape methods in the official docs for details: Shapes.AddPicture and Shapes.AddTextbox.

Example VBA (change the sheet name, range and image path as needed):

Sub InsertPicAndTextbox()
    Dim ws As Worksheet
    Dim rng As Range
    Dim pic As Shape
    Dim tb As Shape
    Dim imgPath As String

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set rng = ws.Range("A1:H1")   ' adjust to A1:A8 if you meant rows instead of columns
    imgPath = "C:\full\path\to\image.jpg"

    Set pic = ws.Shapes.AddPicture(Filename:=imgPath, LinkToFile:=False, SaveWithDocument:=True, _
                                   Left:=rng.Left, Top:=rng.Top, Width:=rng.Width, Height:=rng.Height)
    pic.LockAspectRatio = msoFalse
    pic.ZOrder msoSendToBack

    Set tb = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, rng.Left + 6, rng.Top + 6, rng.Width - 12, rng.Height - 12)
    tb.TextFrame.Characters.Text = "Your text here"
    tb.Line.Visible = msoFalse
    tb.Fill.Visible = msoFalse
    tb.ZOrder msoBringToFront
End Sub

Quick tips: verify the image path, adjust the Range to A1:H1 or A1:A8 depending on which dimension you intended, and change the small margin (the +6 / -12 values) to suit. If you want a true form/ActiveX textbox instead of a drawing shape, use ActiveSheet.OLEObjects.Add with ClassType "Forms.TextBox.1".

Recommended Answers

All 3 Replies

Member Avatar for cguan_77

hi don't know if this is what you mean.. insert your desired bitmap... then put the textbox on it.. then put the code on vba like this..

Sub fox()
TextBox1.Text = "ok"
End Sub
Member Avatar for dinilkarun

I want to do the following in excel sheet fully using VB code.

1. Placin 1 single bitmap from (1,1) to (1,8) cell
2. Placing a text box over it.
3.filling the textbox with text.

THe above solution is not clear.

Regards,
Dinil

Member Avatar for cguan_77

pls. checked attached file..check the macro...thanks..

Bok1.zip (9.67 KB)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.