Excel Add Images Sets For Ranges in VB.NET

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

image1.gif

image2.gif
 

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

image3.gif
 

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

image4.gif

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

Step 5: Press F5 to run the application.
 

Step 6: Macros window will open, here check the macro name and hit the run button

run-vba-application-in-windows8.gif
 

Step 7: You output will shows on Microsoft Excel 2010

vba-output-in-windows8.gif

I hope you like this article and want to try yourself..

Thank You...

Up Next
    Ebook Download
    View all
    Learn
    View all