Excel Add Images Sets For Ranges in VB.NET

Alice H
12y
16.3k
0
0

Article

What is Range

A Range object can be a single cell, a row or column, a selection of cells, or a 3D range, the Range method is the most straightforward way to identify a cell or range. Range method is represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range.

The most common method that I see used to set the value of a cell from VBA and you will see the working of this method later in this article, method is this:

Syntax

Range("B1").Value = 1111

Range("B1").Value = "ABC"

Microsoft Office 2010 gives you the tools needed to create powerful applications. The Microsoft Visual Basic for Applications (VBA) code samples can assist you in creating your own applications that perform specific functions or as a starting point to create more complex solutions.

Lets Create an application

Step 1: Start Microsoft Excel 2010

Step 2: Now press Alt + F11 to open Microsoft Visual Basic for Applications

Step 3: Now choose Sheet1 to write your code from the project window

Step 4: Write the following code in code window

Sub ImagesSetsForRanges()
    Dim i As Integer
    Dim rng As Range
    For i = 1 To 8
        rng = SetTextRange(i)
        Select Case i
            Case 1
                AddPictureInRange("C:\Users\manish\Documents\My Received Files\shalini juneja\icons\icons\Add.gif", _
                Range("A1:A2"))
            Case 2
                AddPictureInRange("C:\Users\manish\Documents\My Received Files\shalini juneja\icons\icons\Backword.gif", _
                Range("A5:A6"))
            Case 3
                AddPictureInRange("C:\Users\manish\Documents\My Received Files\shalini juneja\icons\icons\Forword.gif", _
                Range("A9:A10"))
            Case 4
                AddPictureInRange("C:\Users\manish\Documents\My Received Files\shalini juneja\icons\icons\Pause.gif", _
                Range("A13:A14"))
            Case 5
                AddPictureInRange("C:\Users\manish\Documents\My Received Files\shalini juneja\icons\icons\Play.gif", _
                Range("A17:A18"))
            Case 6
                AddPictureInRange("C:\Users\manish\Documents\My Received Files\shalini juneja\icons\icons\Refresh.gif", _
                Range("A21:A22"))
            Case 7
                AddPictureInRange("C:\Users\manish\Documents\My Received Files\shalini juneja\icons\icons\Stop.gif", _
                Range("A25:A26"))
            Case 8
                AddPictureInRange("C:\Users\manish\Documents\My Received Files\shalini juneja\icons\icons\Volume.gif", _
                Range("A29:A30"))
        End Select
    Next i

End Sub

Sub AddPictureInRange(PictureFileName As String, TargetCells As Range)

    Dim q As Object, t As Double, l As Double, w As Double, h As Double
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    If Dir(PictureFileName) = "" Then Exit Sub

    q = ActiveSheet.Pictures.Insert(PictureFileName)

    With TargetCells
        t = .Top
        l = .Left
        w = .Offset(5, .Columns.Count).Left - .Left
        h = .Offset(.Rows.Count, 0).Top - .Top
    End With

    With q
        .Top = t
        .Left = l
        .Width = w
        .Height = h
    End With
    q = Nothing
End Sub

Function SetTextRange(col As Integer) As Range
    Dim text1 As Range
    text1 = Cells(1, 2)
    text1.Value = "ADD Button"

    Dim text2 As Range
    text2 = Cells(5, 2)
    text2.Value = "Backward Button"

    Dim text3 As Range
    text3 = Cells(9, 2)
    text3.Value = "Forward Button"

    Dim text4 As Range
    text4 = Cells(13, 2)
    text4.Value = "Pause Button"

    Dim text5 As Range
    text5 = Cells(17, 2)
    text5.Value = "Play Button"

    Dim text6 As Range
    text6 = Cells(21, 2)
    text6.Value = "Refresh Button"

    Dim text7 As Range
    text7 = Cells(25, 2)
    text7.Value = "Stop Button"

    Dim text8 As Range
    text8 = Cells(29, 2)
    text8.Value = "Volume Button"
End Function