userform wont overwrite data

[jimmyhendix]

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.

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

 

[Andrew Poulsom]

Isn't it the code for the Submit button that we need to see?

 

[jimmyhendix]

My appologies, I am still very new at this, here is the submit buttons code

Private Sub cmdSubmit_Click()
'Check where the data should be logged
    UserForm1.random1.Text = UserForm1.tbxDuctRun1.Text + UserForm1.tbxDuctSec1.Text
    With Worksheets("Data").Range("A:A")
    
            'Send the input data to the correct place on the data sheet
        Set g = .Find(random1.Text, LookIn:=xlValues, lookat:=xlWhole)
                        
            If Not g Is Nothing Then
                g.Offset(tbxFittingNumber.Value, 2).Value = tbxFittingCode.Value
                g.Offset(tbxFittingNumber.Value, 3).Value = tbxDuctRun1.Value
                g.Offset(tbxFittingNumber.Value, 4).Value = tbxDuctSec1.Value
                g.Offset(tbxFittingNumber.Value, 5).Value = tbxFittingNumber.Value
                g.Offset(tbxFittingNumber.Value, 7).Value = tbxNumberof.Value
                g.Offset(tbxFittingNumber.Value, 8).Value = tbxParam1.Value
                g.Offset(tbxFittingNumber.Value, 9).Value = tbxParam2.Value
                g.Offset(tbxFittingNumber.Value, 10).Value = tbxParam3.Value
                g.Offset(tbxFittingNumber.Value, 11).Value = tbxParam4.Value
                g.Offset(tbxFittingNumber.Value, 12).Value = tbxParam5.Value
            End If
    End With
    
    'First we must populate the run and section numbers of userform2 correctly
    UserForm2.tbxDuctRun2.Value = UserForm1.tbxDuctRun1.Value
    UserForm2.tbxDuctSec2.Text = Format(UserForm1.tbxDuctSec1.Value, "00")
    
    Unload Me
    
    
    
    UserForm2.Show
       
    
End Sub

Thank you Andrew

 

[Andrew Poulsom]

I don't see why that shouldn't work provided that what's in random1 exists in column A on worksheet Data.

 

[jimmyhendix]

Thanks, Ill check random1

regards
jim