PDA

View Full Version : Inserting image to Cell from User Form



hdevadiga
03-02-2009, 12:35 AM
Dear User,

I have a query regarding this.. Myself Created a form which have a option to add image. whtever Data entry u do i get save to sheet1..

I want this when a user do data entry he can add image and save to the particular column in Excel Sheet... Everytime when user make a fresh entry that image get save in to excel sheet with the data format which is mentioned below

Column A - Dist Name
Column B - Office Address
.
.
.
.
.
Column T - Image

The Code is like this:


Private Sub cmdsub_Click()
Dim iRow As String
Dim ws As Worksheet

Set ws = Worksheets("GI")

iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Cells(iRow, 1).Value = Me.txtdisn.Value
ws.Cells(iRow, 2).Value = Me.txtcont.Value
ws.Cells(iRow, 3).Value = Me.txtoffadd.Value
ws.Cells(iRow, 4).Value = Me.txtofftel.Value
ws.Cells(iRow, 5).Value = Me.txtmobile.Value
ws.Cells(iRow, 6).Value = Me.txtresadd.Value
ws.Cells(iRow, 7).Value = Me.txtrestel.Value
ws.Cells(iRow, 8).Value = Me.txtemail.Value
ws.Cells(iRow, 9).Value = Me.txtdisdob.Value
ws.Cells(iRow, 10).Value = Me.txtwifen.Value
ws.Cells(iRow, 11).Value = Me.txtwifedob.Value
ws.Cells(iRow, 12).Value = Me.txtwa.Value
ws.Cells(iRow, 13).Value = Me.txtnok.Value
ws.Cells(iRow, 14).Value = Me.txtc1n.Value
ws.Cells(iRow, 15).Value = Me.txtc1dob.Value
ws.Cells(iRow, 16).Value = Me.txtc2n.Value
ws.Cells(iRow, 17).Value = Me.txtc2dob.Value
ws.Cells(iRow, 18).Value = Me.txtc3n.Value
ws.Cells(iRow, 19).Value = Me.txtc3dob.Value
ws.Cells(iRow, 20).Value = Me.Image1.Picture
With Me
.txtdisn.Value = ""
.txtcont.Value = ""
.txtoffadd.Value = ""
.txtofftel.Value = ""
.txtmobile.Value = ""
.txtresadd.Value = ""
.txtrestel.Value = ""
.txtemail.Value = ""
.txtdisdob.Value = ""
.txtwifen.Value = ""
.txtwifedob.Value = ""
.txtwa.Value = ""
.txtnok.Value = ""
.txtc1n.Value = ""
.txtc1dob.Value = ""
.txtc2n.Value = ""
.txtc2dob.Value = ""
.txtc3n.Value = ""
.txtc3dob.Value = ""
.Image1.Object
End With
End Sub

nst1107
03-02-2009, 10:38 AM
In your cmdaddpic_Click() sub, move the line "Dim fileToOpen" to the top of your module so that it is a global variable. Then, in your cmdsub_Click() sub, changews.Cells(iRow, 20).Value = Me.Image1.Pictureto If Not fileToOpen = False Then
Dim insertImage As Shape
Set insertImage = ws.Shapes.AddPicture(fileToOpen, msoFalse, msoTrue, ws.Cells(iRow, 20).Left, ws.Cells(iRow, 20).Top, ws.Cells(iRow, 20).Width, ws.Cells(iRow, 20).Height)
End If

Note that this will make all your pictures a really odd size, but they will not overlap.

Also, in your cmdsub_Click() sub, change .Image1 = ""
to.Image1.Picture = LoadPicture("")