jimmyhendix
New Member
- Joined
- Jan 4, 2010
- Messages
- 18
Hi all,
I am putting together some userforms to make the input of data into a design sheet faster. At the moment, userform 2 is launched from various buttons, and depending on which is clicked, some variables are set. Userform2 has 5 text boxes showing loss coeffs, next to each are add, edit and remove buttons. Add button clears the respective line on a sheet called "Data", then brings up userform1, the user gives certain inputs and the submit button sends this info to "Data". Remove button clears the info on "Data" (in that row at least).
Now my problem is with the Edit button.
when edit is clicked, all the textboxes on userform1 are populated from the "Data" sheet, which is fine and working, but when i click the same submit button, it won't overwrite the info in "Data"
I have tried clearing row in "Data" after populating the textboxes, but then it just seems clear the row, even after I click submit.
My appologies for the long and drawn out explanation of where i am, but any help here would be greatly appreciated
Thanks in advance
Jim
I am putting together some userforms to make the input of data into a design sheet faster. At the moment, userform 2 is launched from various buttons, and depending on which is clicked, some variables are set. Userform2 has 5 text boxes showing loss coeffs, next to each are add, edit and remove buttons. Add button clears the respective line on a sheet called "Data", then brings up userform1, the user gives certain inputs and the submit button sends this info to "Data". Remove button clears the info on "Data" (in that row at least).
Now my problem is with the Edit button.
when edit is clicked, all the textboxes on userform1 are populated from the "Data" sheet, which is fine and working, but when i click the same submit button, it won't overwrite the info in "Data"
I have tried clearing row in "Data" after populating the textboxes, but then it just seems clear the row, even after I click submit.
Code:
Private Sub Edit()
''FIRST CALL THE VALUES INTO THE TEXTBOXES BY A REVERSE OF THE INPUT
UserForm2.random2.Text = UserForm2.tbxDuctRun2.Text + UserForm2.tbxDuctSec2.Text
With Worksheets("Data").Range("A:A")
Set w = .Find(random2.Text, LookIn:=xlValues, lookat:=xlWhole)
'Fitting Code
UserForm1.tbxFittingCode.Value = w.Offset(tbxFittingNumber2.Value, 2).Value
UserForm1.tbxFittingCode.Visible = True
UserForm1.lblFittingCode.Visible = True
'Duct Run
UserForm1.tbxDuctRun1.Value = w.Offset(tbxFittingNumber2.Value, 3).Value
UserForm1.tbxDuctRun1.Visible = True
'Duct Sec
UserForm1.tbxDuctSec1.Value = w.Offset(tbxFittingNumber2.Value, 4).Value
UserForm1.tbxDuctSec1.Visible = True
'Fitting number
UserForm1.tbxFittingNumber.Value = w.Offset(tbxFittingNumber2.Value, 5).Value
UserForm1.tbxFittingNumber.Visible = True
'Number of
UserForm1.tbxNumberof.Value = w.Offset(tbxFittingNumber2.Value, 7).Value
UserForm1.tbxNumberof.Visible = True
UserForm1.lblNumberof.Visible = True
'Param1
UserForm1.tbxParam1.Value = w.Offset(tbxFittingNumber2.Value, 8).Value
If UserForm1.tbxParam1.Value <> "" Then
UserForm1.tbxParam1.Visible = True
Else
UserForm1.tbxParam1.Visible = False
End If
'Param2
UserForm1.tbxParam2.Value = w.Offset(tbxFittingNumber2.Value, 9).Value
If UserForm1.tbxParam2.Value <> "" Then
UserForm1.tbxParam2.Visible = True
Else
UserForm1.tbxParam2.Visible = False
End If
'Param3
UserForm1.tbxParam3.Value = w.Offset(tbxFittingNumber2.Value, 10).Value
If UserForm1.tbxParam3.Value <> "" Then
UserForm1.tbxParam3.Visible = True
Else
UserForm1.tbxParam3.Visible = False
End If
'Param4
UserForm1.tbxParam4.Value = w.Offset(tbxFittingNumber2.Value, 11).Value
If UserForm1.tbxParam4.Value <> "" Then
UserForm1.tbxParam4.Visible = True
Else
UserForm1.tbxParam4.Visible = False
End If
'Param5
UserForm1.tbxParam5.Value = w.Offset(tbxFittingNumber2.Value, 12).Value
If UserForm1.tbxParam5.Value <> "" Then
UserForm1.tbxParam5.Visible = True
Else
UserForm1.tbxParam5.Visible = False
End If
End With
''THEN SHOW THE RELEVANT PARAMINFO TEXTBOXES AND POPULATE THE COMBOBOXES FROM THIS TOO
With Worksheets("Fitting Information").Range("E:E")
Set v = .Find(UserForm1.tbxFittingCode.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not v Is Nothing Then
UserForm1.cmbFittings.Value = v.Offset(0, 1).Value
UserForm1.tbxParamInfo1.Value = v.Offset(0, 3).Value
UserForm1.tbxParamInfo2.Value = v.Offset(0, 4).Value
UserForm1.tbxParamInfo3.Value = v.Offset(0, 5).Value
UserForm1.tbxParamInfo4.Value = v.Offset(0, 6).Value
UserForm1.tbxParamInfo5.Value = v.Offset(0, 7).Value
End If
End With
UserForm1.cmbFittingType.Visible = False
UserForm1.cmbFittings.Locked = True
''THIS ONE JUST CLEARS THE LINE AND LEAVES A BLANK AFTER "DONE" IS CLICKED
''With Worksheets("Data").Range("A:A")
''Set r = .Find(random2.Text, LookIn:=xlValues, lookat:=xlWhole)
''r.Offset(tbxFittingNumber2.Value, 6).ClearContents
''End With
Unload Me
UserForm1.Show
End Sub
My appologies for the long and drawn out explanation of where i am, but any help here would be greatly appreciated
Thanks in advance
Jim