How to select the position of a picture when inserting Picture in MS Excel (1 Viewer)

fulltime

Registered User.
Local time
Today, 17:57
Joined
Mar 24, 2006
Messages
56
Hi all,

I want to insert a picutre into my MS excel spreadsheet. However, I want to dictate the position within a cell that the picture shld appear in.. For example, i wan it to be right justified when it is inserted..Is it possible to be done?

Thks
FT :)
 

HaHoBe

Locomotive Breath
Local time
Today, 10:57
Joined
Mar 1, 2002
Messages
233
Hi, FT,

might be wrong about this but to my knowledge you can only pass Left, Top, Width, and Height for a position. This will lead to finding the Left of the next cell to the right and the Width of the picture to get the Left starting point for the picture. And I pretty much doubt that it might be possible to dictate the position within a cell as pictures will be floating on top of the cells... ;)

Ciao,
Holger
 

fulltime

Registered User.
Local time
Today, 17:57
Joined
Mar 24, 2006
Messages
56
HaHoBe said:
Hi, FT,

might be wrong about this but to my knowledge you can only pass Left, Top, Width, and Height for a position. This will lead to finding the Left of the next cell to the right and the Width of the picture to get the Left starting point for the picture. And I pretty much doubt that it might be possible to dictate the position within a cell as pictures will be floating on top of the cells... ;)

Ciao,
Holger

Hi Holger,

I want the picture to be right justified within a cell, like u mentioned, pass the position? but how do i really pass the position? can u kindly elaborate? thks..

FT :)
 

shades

Registered User.
Local time
Today, 04:57
Joined
Mar 25, 2002
Messages
516
Holger is correct. Pictures are on the worksheet and do not become part of a cell's contents. After inserting a picture into a sheet you position it over a cell with code like:
Code:
    Sheet1.Shapes("Picture 1").Left = Range("D4").Left
    Sheet1.Shapes("Picture 1").Top = Range("D4").Top

EDIT: Corrected reference
________
Glk-Class
 
Last edited:

HaHoBe

Locomotive Breath
Local time
Today, 10:57
Joined
Mar 1, 2002
Messages
233
Hi, FT,

as my old Excel97 does not support the Right corresponding to a cell you might take the following code to get the job done:

Code:
Option Explicit

Sub procInsertPictureFT()
Dim strName As String
Dim strPath As String
Dim rngCell As Range
Dim dblLeft As Double
Dim dblWidth As Double

Set rngCell = ActiveCell
' capture present path and directory
strPath = CurDir
' choose your picture
strName = Application.GetOpenFilename("JPG-Pictures (*.jpeg; *.jpg), *.jpg; *.jpeg)")
' cancel?
If strName <> "False" Then
  ' Import
  ActiveSheet.Pictures.Insert(strName).Select
  With Selection.ShapeRange
    dblWidth = .Width
    dblLeft = rngCell.Offset(0, 1).Left
    .Left = dblLeft - dblWidth
  End With
End If
Set rngCell = Nothing
' go back to starting path and directory
ChDrive Left(strPath, 1)
ChDir strPath
End Sub
Ciao,
Holger
 

shades

Registered User.
Local time
Today, 04:57
Joined
Mar 25, 2002
Messages
516
Ah, nuts. I wasn't paying attention, Thanks, Holger. Anyway, it should be LEFT, not RIGHT as I originally posted.
________
FIND DISPENSARY
 
Last edited:

fulltime

Registered User.
Local time
Today, 17:57
Joined
Mar 24, 2006
Messages
56
thks brothers for providing such wonderful solution.. it works as wat i wanted..

Hope u guys won't mind, I have another question pertaining to the same topic of objects..

For example, i want to select an object that is located in Cell(3,30), anyway i can select the object? or i can only select an object based on the object's name?

Thks agn
FT :)
 

shades

Registered User.
Local time
Today, 04:57
Joined
Mar 25, 2002
Messages
516
It depends. I had set up a traffic light system with 144 objects and named them using the cell references (underneath) as the suffix for each name. Then I could loop through all 144 objects using what was in each cell as the value to determine the color and shape of each object based on cell value. Worked great. And using code, it took less than 2 seconds to update.
________
Tickford
 
Last edited:

fulltime

Registered User.
Local time
Today, 17:57
Joined
Mar 24, 2006
Messages
56
shades said:
It depends. I had set up a traffic light system with 144 objects and named them using the cell references (underneath) as the suffix for each name. Then I could loop through all 144 objects using what was in each cell as the value to determine the color and shape of each object based on cell value. Worked great. And using code, it took less than 2 seconds to update.

can u give an example of how to name the objects using the cell references as suffix for each name? thks
 

shades

Registered User.
Local time
Today, 04:57
Joined
Mar 25, 2002
Messages
516
objA2 (refers to cell A2)
objA3 (refers to cell A3)

etc.

Then if you use only the column A, you can set up a loop in VBA

Code:
Sub OriginalValueCheck()
'This checks the cells in Column A on worksheet Work (rows 2 through end of column)
Application.ScreenUpdating = False
    Dim lngCol As Long
    Dim lngLastRow as Long
    Sheets("Work").Select
    For lngCol = 2 To lngLastRow
        Select Case Cells(lngCol, 1).Value
        Case Is < 0: RedAutoShape lngCol
        Case Is > 0: GreenAutoShape lngCol
        Case 0: OrangeAutoShape lngCol
        End Select
    Next i
Application.ScreenUpdating = True
End Sub

Sub RedAutoShape(lngCol)
    Sheets("Highlights").Select
    With ActiveSheet.Shapes("objA" & lngCol )
        .AutoShapeType = msoShapeDownArrow
        .Fill.Visible = msoTrue
        .Fill.ForeColor.SchemeColor = 10
        .Line.ForeColor.SchemeColor = 10
        .Rotation = 0#
    End With
    Sheets("Work").Select

End Sub
Sub GreenAutoShape(lngCol)
    Sheets("Highlights").Select
    With ActiveSheet.Shapes("objA" & lngCol)
        .AutoShapeType = msoShapeUpArrow
        .Fill.Visible = msoTrue
        .Fill.ForeColor.SchemeColor = 57
        .Line.ForeColor.SchemeColor = 57
        .Rotation = 0#
    End With
    Sheets("Work").Select

End Sub
 
Sub OrangeAutoShape(lngCol)
    Sheets("Highlights").Select
    With ActiveSheet.Shapes("objA" & lngCol)
        .AutoShapeType = msoShapeLeftRightArrow
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = 52
        .Line.ForeColor.SchemeColor = 52
        .Rotation = 0#
    End With
    Sheets("Work").Select

End Sub

I should mention that I had the cell references on one worksheet ("Work") and the traffic lights on another ("Highlights") that matched with those. Easier to maintain control.
________
Half-Baked
 
Last edited:

shades

Registered User.
Local time
Today, 04:57
Joined
Mar 25, 2002
Messages
516
If you want to include more columns, then include a loop outside the above loop, something like (simplified):

Code:
Dim i as Long, k as Long, LastRow as Long, LastCol as Long
For k = 1 to LastCol
    For i = 1 to LastRow

    Next i
Next k
________
Rhode island marijuana dispensary
 
Last edited:

fulltime

Registered User.
Local time
Today, 17:57
Joined
Mar 24, 2006
Messages
56
thks, i will chk it out.. u been of great help

FT:)
 

Users who are viewing this thread

Top Bottom