Update cells from Text Boxes

ElBenno

Board Regular
Joined
Jun 3, 2005
Messages
97
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
If you use a CommandButton on your form you can use something along these lines:

Code:
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

Board Regular
Joined
Jun 3, 2005
Messages
97
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

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Jun 3, 2005
Messages
97

ADVERTISEMENT

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

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Jun 3, 2005
Messages
97

ADVERTISEMENT

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

Board Regular
Joined
Jun 3, 2005
Messages
97
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

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Jun 3, 2005
Messages
97
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,
 

Forum statistics

Threads
1,136,697
Messages
5,677,264
Members
419,682
Latest member
M3one

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