Update cells from Text Boxes

[ElBenno]

Hi,
I have a spread sheet that has 2 work sheets 'Breach Form' and 'Register'.
What i have tried to create is a Form that can be used to update the Register. I have created the form by using text boxes.
I have now come to the point of creating the VBA / Macro that i can assign to a button that will take the data out of the text boxes and insert the data into the register (into the appropriate cells) then delete the content from the text boxes.
I also require a refresh/clear button that can be used to delete the information from the text boxes in the form if a mistake is made.

can some please provide support in this as my VBA / Macro skills are very limited.

Kindest Regards,

Ben

 

[Smitty]

If you use a CommandButton on your form you can use something along these lines:

Private Sub Command Button1_Click()
  Dim ws as worksheet
  Dim i as Integer
     Set ws = Sheets("Register")

     '  Return data to the worksheet
     With ws
        .Range("A1") = TextBox1.Value
        .Range("A2") = TextBox2.Value
     End With

     '  Clear the TextBoxes
     For i =1 to 2
       TextBox(i).Value = ""
     Next i

End Sub

Or did you need the data to be returned toa dynamic range?

Hope that helps,

Smitty

 

[ElBenno]

That worked great for the transfer of the text, however the clearing of the text did not work (i will tinker with it). also with the transfer of text is it possible to have it identify where text has already been entered so it puts it into the next row of cells??

kindest regards,

 

[Smitty]

with the transfer of text is it possible to have it identify where text has already been entered so it puts it into the next row of cells??

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">Set</SPAN> LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)
        
        LastRow.Offset(1) = TextBox1.Text
        
        TextBox1 = ""
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Smitty

 

[ElBenno]

Hi Smitty,
this is just what i wanted. one more question though, i have upto 11 text boxes in my form, how can i manipulate the code to do it all text boxes at once?

thanks heaps

 

[Smitty]

this is just what i wanted. one more question though, i have upto 11 text boxes in my form, how can i manipulate the code to do it all text boxes at once?

Take a look at the For...Next statement in the first example.

Smitty

 

[ElBenno]

Hi Smitty,
I cannot see how it is supposed to slot in together as there is 2 range sets.

any more assistance would be great

thanks

 

[ElBenno]

Hi,
Is there someone that can help me put the final steps on this code to allow me to get the information from the form to the table.

thanks

 

[Smitty]

I cannot see how it is supposed to slot in together as there is 2 range sets.

What ranges are you trying to work with? What are your text box names/numbers?

Smitty

 

[ElBenno]

My text boxes are called:

brfTodaysDate
brfDateOfBreach
brfDurationOfBreach
brfDescription
brfIDMethod
brfSignificantTest
brfARDetails
brfRectification
brfPrevention

In your second code example it transfered the text to the next line when i entered a new record. however i could only get that to happen for the brfTodaysDate, I dont know what to sent the range up to get the text boxes to transfer to the register (which ranges from "A" - "I")

kindest regards,