Userform must always display data on Sheet1 while on any other sheet.,..

[chazrab]

This code displays data in Userform1 on the active sheet(in the background [showmodal = false]

Private Sub UserForm_Initialize()

currentrow = 2  'currentrow is a global variable of type Long

lastrow = Sheets("REGISTER").Range("A" & Rows.count).End(xlUp).Row

TextBox1 = Cells(currentrow, 1)
TextBox2 = Cells(currentrow, 2)
TextBox3 = Cells(currentrow, 3)
TextBox4 = Cells(currentrow, 6)
TextBox5 = Cells(currentrow, 7)

Userform1 is fired from a QAT button with Sheet2 or any sheet being the Activesheet. The problem is, the Textboxes are populated with data from Sheet2 or the Activesheet. I don't want that. I want Userform1 to ALWAYS display data from Sheet1 regardless of which sheet I'm on

Another way of saying this is, every time I change the sheet and click the QAT button, it fills the textboxes with data from that active sheet, which gives meaningless information. How do I keep Userform1 to always display data from Sheet1 ?

Thanks for anyone's help.

cr

 

[Fluff]

How about

Private Sub UserForm_Initialize()

currentrow = 2  'currentrow is a global variable of type Long
With Sheets("REGISTER")
   Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
   
   TextBox1 = .Cells(currentrow, 1)
   TextBox2 = .Cells(currentrow, 2)
   TextBox3 = .Cells(currentrow, 3)
   TextBox4 = .Cells(currentrow, 6)
   TextBox5 = .Cells(currentrow, 7)
End With

 

[chazrab]

How about

Private Sub UserForm_Initialize()

currentrow = 2  'currentrow is a global variable of type Long
With Sheets("REGISTER")
   Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
   
   TextBox1 = .Cells(currentrow, 1)
   TextBox2 = .Cells(currentrow, 2)
   TextBox3 = .Cells(currentrow, 3)
   TextBox4 = .Cells(currentrow, 6)
   TextBox5 = .Cells(currentrow, 7)
End With

Thanks Fluff - putting the same code within a With-End With block still does not work. The userform textboxes are filled with data from the active sheet. There has to be some inherent flaw in this code that's causing the Userform not to always use the data on the REGISTER sheet(which is what I want) and NOT the active sheet that appears on the screen
This seems way too simple to give such a problem. What are we missing here ?

Thks, cr

 

[Fluff]

Did you use the code I supplied, or modify your own code?
If the latter did you put the . infront of Cells?

 

[chazrab]

Did you use the code I supplied, or modify your own code?
If the latter did you put the . infront of Cells?

This is the code that does not work

currentrow = 2  'currentrow is a global variable of type Long
With Sheets("REGISTER")
lastrow = Range("A" & Rows.count).End(xlUp).Row
TextBox1 = .Cells(currentrow, 1)
TextBox2 = .Cells(currentrow, 2)
TextBox3 = .Cells(currentrow, 3)
TextBox4 = .Cells(currentrow, 6)
TextBox5 = .Cells(currentrow, 7)
End With

cr

 

[chazrab]

This is the code that does not work

currentrow = 2  'currentrow is a global variable of type Long
With Sheets("REGISTER")
lastrow = Range("A" & Rows.count).End(xlUp).Row
TextBox1 = .Cells(currentrow, 1)
TextBox2 = .Cells(currentrow, 2)
TextBox3 = .Cells(currentrow, 3)
TextBox4 = .Cells(currentrow, 6)
TextBox5 = .Cells(currentrow, 7)
End With

cr

...oops made a stupid mistake...your code works perfectly. Embarrassed.
disregard last reply
cr

 

[Fluff]

Deleted, seen post#6