userform wont overwrite data

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.

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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

jimmyhendix

New Member
Joined
Jan 4, 2010
Messages
18
My appologies, I am still very new at this, here is the submit buttons code
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I don't see why that shouldn't work provided that what's in random1 exists in column A on worksheet Data.
 

Forum statistics

Threads
1,181,759
Messages
5,931,887
Members
436,805
Latest member
waseem abbas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top