Adding data with a search userform & then populating another userform with values..

[pierre robinson]

Hi Team.

Bit hard to describe here, but what I am doing is adding an address from a list onto a datasheet, using a userform,(oddly enough called "SearchUF"). So the code adds the new address in Col B using xlup and the remainder of that row is populated using a series of formulas. Which works perfectly well.

What I want to happen after the select command button is clicked on the SearchUF, is for to it to close and another userform (Userform1) to open, populated with that new address and the subsequent fields that have been added via the formulas.

I can get Userform1 to show, but it will not populate the various text boxes.

I also understand that it necessarily needs to be a separate event, but cant figure out the syntax required.

Code as below:

Private Sub cbSelectUpdate_Click()

Dim lastrow As Integer
Dim Address As String


Address = cbSearchAddress.Value
lastrow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row




If Application.WorksheetFunction.CountIf(Sheets("Datasheet").Range("B3:B20000"), Address) > 0 Then
MsgBox "Address has already been added", 0, "Please Check"
Exit Sub


Else


Worksheets("Datasheet").Range("B" & lastrow + 1).Value = cbSearchAddress.Value




End If




Unload SearchUF

Call UpdateUF


End Sub

Private Sub UpdateUF()


Dim Targetrow As String


Targetrow = Sheets("Engine").Range("B1").Value


'lastrow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row




Userform1.AddItem.cbAddress = Sheets("Datasheet").Range("Data_Start").Offset(Targetrow, 1).Value


Userform1.AddItem.tbBeds = Sheets("Datasheet").Range("Data_Start").Offset(Targetrow, 10).Value

 

[sykes]

How about using the ".Show" command, to activate UserForm1, then put your code to populate it, into it's "_Initialize" event:

Private Sub cbSelectUpdate_Click()

Dim lastrow As Integer
Dim Address As String

Address = cbSearchAddress.Value
lastrow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row


If Application.WorksheetFunction.CountIf(Sheets("Datasheet").Range("B3:B20000"), Address) > 0 Then
MsgBox "Address has already been added", 0, "Please Check"
Exit Sub

Else

Worksheets("Datasheet").Range("B" & lastrow + 1).Value = cbSearchAddress.Value

End If

Unload SearchUF

UserForm1.show

End Sub

Then, for userform1:

Private Sub UserForm_Initialize()
Dim Targetrow As String

Targetrow = Sheets("Engine").Range("B1").Value

'lastrow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row

Userform1.AddItem.cbAddress = Sheets("Datasheet").Range("Data_Start").Offset(Targetrow, 1).Value

Userform1.AddItem.tbBeds = Sheets("Datasheet").Range("Data_Start").Offset(Targetrow, 10).Value
End Sub

... not tested, so it may be better to test on a copy of your real work!

 

[pierre robinson]

Yep, hearing you - the issue with that is that Userform1 loads at the same time as the SearchUF.

My logic is that the UF should load after the searchUF closes, but......

VBA...all good fun

Cheers

How about using the ".Show" command, to activate UserForm1, then put your code to populate it, into it's "_Initialize" event:

Private Sub cbSelectUpdate_Click()

Dim lastrow As Integer
Dim Address As String

Address = cbSearchAddress.Value
lastrow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row


If Application.WorksheetFunction.CountIf(Sheets("Datasheet").Range("B3:B20000"), Address) > 0 Then
MsgBox "Address has already been added", 0, "Please Check"
Exit Sub

Else

Worksheets("Datasheet").Range("B" & lastrow + 1).Value = cbSearchAddress.Value

End If

Unload SearchUF

UserForm1.show

End Sub

Then, for userform1:

Private Sub UserForm_Initialize()
Dim Targetrow As String

Targetrow = Sheets("Engine").Range("B1").Value

'lastrow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row

Userform1.AddItem.cbAddress = Sheets("Datasheet").Range("Data_Start").Offset(Targetrow, 1).Value

Userform1.AddItem.tbBeds = Sheets("Datasheet").Range("Data_Start").Offset(Targetrow, 10).Value
End Sub

... not tested, so it may be better to test on a copy of your real work!