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

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

Step 5: Press F5 to run the application.

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

Step 7: You output will shows on Microsoft Excel 2010

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

Thank You...