Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

populate data on userform for hidden sheet

0

Hello

I  would  help  for  this  problem , what  I  want  populating  data on  userform  for  sheet  hidden . the  hidden sheet is sheet1. currently  the  code   doesn't  show  any  thing .

Private Sub ComboBox1_Change()
Dim NAT As Long
Dim sh As Sheets
Dim Name As String
Dim myrange As Range
On Error Resume Next
Sheets("sheet1").Visible = True
Name = Me.ComboBox1.Value
With Sheets("sheet1")
Set myrange = Range("B3:M9")
TextBox1.Value = Application.WorksheetFunction.VLookup(Name, myrange, 2, False)
TextBox2.Value = Application.WorksheetFunction.VLookup(Name, myrange, 3, False)
End With
Sheets("sheet1").Visible = False
End Sub

thanks

excel
MS Office Version: 2016
posted by: Kalil (rep: 36) on Fri Sep 23, 2022 at 11:30 am
Answer
Discuss

Answers

0
Selected Answer

Hi Kalil

If you refer to Range("B3:M9") without tying it to a sheet, VBA assumes it's on the active sheet (and you opened the UserForm with sheet2 active so it tries Vlookup in the empty cells on sheet2).

The problem is just one missing dot (or period) mark before Range in the section below (so that the Range statement is linked with the With statement where you define the sheet as sheet1), so it should read (with dot in bold):

With Sheets("sheet1")
    Set myrange = .Range("B3:M9")
    TextBox1.Value = Application.WorksheetFunction.VLookup(Name, myrange, 2, False)
    TextBox2.Value = Application.WorksheetFunction.VLookup(Name, myrange, 3, False)
End With

Unless you want to do more in the With statement (like include .Visible = False to replace the last line of code), you could omit it (and End With) and just use:

Set myrange = Sheets("sheet1").Range("B3:M9")
TextBox1.Value = Application.WorksheetFunction.VLookup(Name, myrange, 2, False)
TextBox2.Value = Application.WorksheetFunction.VLookup(Name, myrange, 3, False)

Hope this fixes things for you.

posted by: John_Ru (rep: 6142) on Tue Sep 27, 2022 at 8:30 am
Discuss

Discussion

Hi, John, 
this is awful !!! 
I was really stupid , OMG!!
how I don't note it . thanks for important spotting , it made big difference to work ,despite of  it  was really simple missed.
Kalil (rep: 36) Sep 27, '22 at 8:57 am
No problem Kalil, anyone could make miss that tiny error (I have before!).

Thanks for selecting my Answer.
John_Ru (rep: 6142) Sep 27, '22 at 9:50 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login