Hi,
I am having an issue passing data from a worksheet into a userform. The
situation is that I have two forms. One form being the main form where the
user inputs data and the VBA code enters the data in an excel sheet. Each
time the user enters a set of data(row) and clicks the button to add the
data, the row or set of data gets an index number. So the Sheet would be
something like this,
Index Type Size
1..........3C.....#3/0
2..........3C.....#2/0 ...etc

Now, I have a button on my main form(Userform) called modify that opens up
another form. The code that I have to open my Modify form(frmMod) is:

--(This is in the UserForm code)--
Private Sub cmdModify_Click()
UserForm.Hide
Load frmMod
frmMod.Show
End Sub

Now the modify form is where the user selects the index for the row of data
they want to modify. So the user selects this number from a combo box
(cmbIndex). The user then selects an ok button which runs VBA code to go to
the user inputted data, match the index numbers and then populate the main
form with the data from the row and close the modify form. Here is the OK
event code for the modify form:

--(This is in the frmMod code)--
Private Sub cmdOK_Click()
Dim x As Integer
Dim y As Integer
If cmbIndex.Value < "" Then
Range(REF_CELL).Select 'Start at the top of the
index list
ActiveCell.Offset(1, 0).Select 'Move down one cell to
go past the header

Do Until IsEmpty(ActiveCell) = True 'Scroll down until at
the end of the index list
x = ActiveCell.Value 'x= current index value
as code moves down list
y = cmbIndex.Value 'y= user selected index
value
If x = y Then 'When index matches user
selected index

**** UserForm.cmbVoltage.Value = "5kV"
UserForm.txtQuantity.Text = ActiveCell.Offset(0, 1).Value
UserForm.cmbType.Value = "3C"
UserForm.cmbSize.Value = ActiveCell.Offset(0, 2).Value
UserForm.txtDescription.Text = ActiveCell.Offset(0, 3).Value
UserForm.cmbControlCable.Value = ActiveCell.Offset(0, 4).Value
Exit Do

End If
ActiveCell.Offset(1, 0).Select 'Move down a cell every
loop iteration
Loop
UserForm.Show 'Show the userform
Unload frmMod 'Close the Modify form
End If
End Sub

The issue is that when the code goes to assign the values at the line
denoted with '****', it goes to the userform_initialize event which I do not
think should happen since I only used userform.hide. Also, the code does not
continue running past the **** line since it reinitializes the userform. This
is behavior I am not expecting and I am at a loss as to how to correct it or
as to why it is happening. Sorry bout the long msg but wanna make sure any
helpful info is there.
Thanks for any help in advance,
--
Aaron

Sorry, but I don't have time to read all of your code. Anyway, this is how I
pass data from a sheet to a UserForm:

Name a TextBox, such as 'Number'

VBA Code under UserForm:
Private Sub UserForm_Activate()
On Error Resume Next
Number.Text = Cells(1, 1) ' (1, 1) = Down 1 cell and over one cell, so A1
Account.Text = Cells(2, 1) ' (2, 1) = Down 2 cells and over one cell, so A2
'And so on and so forth
On Error Resume Next
End Sub

If you want to add formatting, use this technique:
TextBox1.Text = Format(Cells(1, 1), "#,##0.00") ' For decimalization
TextBox1.Text = Format(Cells(1, 1), "##0.00%") ' For percentages

Regards,
Ryan---
--
RyGuy


"Aaron" wrote:

Hi,
I am having an issue passing data from a worksheet into a userform. The
situation is that I have two forms. One form being the main form where the
user inputs data and the VBA code enters the data in an excel sheet. Each
time the user enters a set of data(row) and clicks the button to add the
data, the row or set of data gets an index number. So the Sheet would be
something like this,
Index Type Size
1..........3C.....#3/0
2..........3C.....#2/0 ...etc

Now, I have a button on my main form(Userform) called modify that opens up
another form. The code that I have to open my Modify form(frmMod) is:

--(This is in the UserForm code)--
Private Sub cmdModify_Click()
UserForm.Hide
Load frmMod
frmMod.Show
End Sub

Now the modify form is where the user selects the index for the row of data
they want to modify. So the user selects this number from a combo box
(cmbIndex). The user then selects an ok button which runs VBA code to go to
the user inputted data, match the index numbers and then populate the main
form with the data from the row and close the modify form. Here is the OK
event code for the modify form:

--(This is in the frmMod code)--
Private Sub cmdOK_Click()
Dim x As Integer
Dim y As Integer
If cmbIndex.Value < "" Then
Range(REF_CELL).Select 'Start at the top of the
index list
ActiveCell.Offset(1, 0).Select 'Move down one cell to
go past the header

Do Until IsEmpty(ActiveCell) = True 'Scroll down until at
the end of the index list
x = ActiveCell.Value 'x= current index value
as code moves down list
y = cmbIndex.Value 'y= user selected index
value
If x = y Then 'When index matches user
selected index

**** UserForm.cmbVoltage.Value = "5kV"
UserForm.txtQuantity.Text = ActiveCell.Offset(0, 1).Value
UserForm.cmbType.Value = "3C"
UserForm.cmbSize.Value = ActiveCell.Offset(0, 2).Value
UserForm.txtDescription.Text = ActiveCell.Offset(0, 3).Value
UserForm.cmbControlCable.Value = ActiveCell.Offset(0, 4).Value
Exit Do

End If
ActiveCell.Offset(1, 0).Select 'Move down a cell every
loop iteration
Loop
UserForm.Show 'Show the userform
Unload frmMod 'Close the Modify form
End If
End Sub

The issue is that when the code goes to assign the values at the line
denoted with '****', it goes to the userform_initialize event which I do not
think should happen since I only used userform.hide. Also, the code does not
continue running past the **** line since it reinitializes the userform. This
is behavior I am not expecting and I am at a loss as to how to correct it or
as to why it is happening. Sorry bout the long msg but wanna make sure any
helpful info is there.
Thanks for any help in advance,
--
Aaron