Reading an excel worksheet can be very complex when it involve a lot of data to read. You need to scroll to the left just to read the related information to the entity. This can be overcome or simplified by using VBA Userform. In this article, I will show you on how to get cell value in Excel VBA.
How To Get Cell Value In Excel VBA
Before you follow this guide, ensure that you already enable Developer mode and create userform in Excel.
Straight forward, you can use below code to get cell value in Excel. Replace “sheetname” with your excel Sheet’s name (eg; Sheet1, Sheet2 etc.) and replace “cellrange” with cell location (eg; A2, B2 etc.)
Sheets(“sheetname”).Range(“cellrange”)
To give you a better view and understanding on how to use it, let me give you an example.
Let’s say we have Excel table with a person details like below:
And you want to populate the data in userform view as below:
Assume that you want to get the value for the first person (“Geek”), the cell range or location for the cell value will be like:
- FirstName(A2)
- LastName(B2)
- Address(C2)
- City(D2)
- State(E2)
- Country(F2)
- ZIP(G2)
- Contact(H2)
- Job(I2)
- Nationality(J2)
The VBA code will be like:
Private Sub UserForm_Initialize()
TextBox1.Text = Sheets(“Sheet1”).Range(“A2”) ‘FirstName
TextBox2.Text = Sheets(“Sheet1”).Range(“B2”) ‘LastName
TextBox3.Text = Sheets(“Sheet1”).Range(“C2”) ‘Address
TextBox4.Text = Sheets(“Sheet1”).Range(“D2”) ‘City
TextBox5.Text = Sheets(“Sheet1”).Range(“E2”) ‘State
TextBox6.Text = Sheets(“Sheet1”).Range(“F2”) ‘Country
TextBox7.Text = Sheets(“Sheet1”).Range(“G2”) ‘ZIP
TextBox8.Text = Sheets(“Sheet1”).Range(“H2”) ‘Contact
TextBox9.Text = Sheets(“Sheet1”).Range(“I2”) ‘Job
TextBox10.Text = Sheets(“Sheet1”).Range(“J2”) ‘Nationality
End Sub
Click on play or execute button.
There, the information for “Geek” is shown on the userform.
If you want to read for the next person or user details, just change the range number for cell range to 3 as below:
TextBox1.Text = Sheets(“Sheet1”).Range(“A3”) ‘FirstName
TextBox2.Text = Sheets(“Sheet1”).Range(“B3”) ‘LastName
TextBox3.Text = Sheets(“Sheet1”).Range(“C3”) ‘Address
TextBox4.Text = Sheets(“Sheet1”).Range(“D3”) ‘City
TextBox5.Text = Sheets(“Sheet1”).Range(“E3”) ‘State
TextBox6.Text = Sheets(“Sheet1”).Range(“F3”) ‘Country
TextBox7.Text = Sheets(“Sheet1”).Range(“G3”) ‘ZIP
TextBox8.Text = Sheets(“Sheet1”).Range(“H3”) ‘Contact
TextBox9.Text = Sheets(“Sheet1”).Range(“I3”) ‘Job
TextBox10.Text = Sheets(“Sheet1”).Range(“J3”) ‘Nationality
It will show the details for “Harry”.
It’s very easy right.
You can put the action on button, like when you click the button, it will read the cell value.
Just add a button, let’s say name it “Read”. Then put action onClick, and put the code as below:
Private Sub Read_Click() TextBox1.Text = Sheets(“Sheet1”).Range(“A3”) TextBox2.Text = Sheets(“Sheet1”).Range(“B3”) TextBox3.Text = Sheets(“Sheet1”).Range(“C3”) TextBox4.Text = Sheets(“Sheet1”).Range(“D3”) TextBox5.Text = Sheets(“Sheet1”).Range(“E3”) TextBox6.Text = Sheets(“Sheet1”).Range(“F3”) TextBox7.Text = Sheets(“Sheet1”).Range(“G3”) TextBox8.Text = Sheets(“Sheet1”).Range(“H3”) TextBox9.Text = Sheets(“Sheet1”).Range(“I3”) TextBox10.Text = Sheets(“Sheet1”).Range(“J3”) End Sub
But in real situation, you should not edit the VBA code directly just to get the next value right? All you need is something that can get the next value, without need to change the code.
That means, you need a features to read the cell value dynamically. To proceed with dynamically get the cell value, you may refer this guide.
Thanks for reading this article. I hope you find it helpful.
IT Security / Cyber Security Experts.
Technology Enthusiasm.
Love to read, test and write about IT, Cyber Security and Technology.
The Geek coming from the things I love and how I look.