How to save the text of a textbox to a cell

[borreltijd]

Hi everybody,

I need some help with the following.

I have an excel sheet with some buttons and some methods attached to it, everything works fine.

The first worksheet of my excel file is some sort of user interface, it contains among other thins a textbox which I labeled as: 'TextBox'

Next to this textbox is a button which a called ' Save'

I would like that when pressing the button the text in the textbox is saved to a cell in another worksheet of the same excel file.

So in easy lamer code something like this:


Sub SaveText()
TheTextInTheTextBox = TextBox
Worksheets("LogBook").Range("A1").Value = TheTextInTheTextBox
End Sub
' end of code

The save button is used to call the SaveText()


I tried it with the macro recorder with resulted in

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/29/2008 by gerri00e
'

'
    ActiveSheet.Shapes("TextBox").Select
    Selection.Characters.Text = "sfsfsdf dfgdfgd dfgdgdf dfgdgd dfgdgdgdg " & Chr(10) & ""
    With Selection.Characters(Start:=1, Length:=42).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Sheets("LogBook").Select
    ActiveSheet.Paste
End Sub

This doesn't really help me.....


Thanks in advance !

 

[Kenneth Hobson]

You could set the ControlSource property for the textbox object to your cell or:

Sub SaveText()
Worksheets("LogBook").Range("A1").Value = TextBox.Value
End Sub

 

[borreltijd]

You could set the ControlSource property for the textbox object to your cell or:

Sub SaveText()
Worksheets("LogBook").Range("A1").Value = TextBox.Value
End Sub

I am sorry but this doesn't work !

 

[Kenneth Hobson]

Not sure what doesn't work means. It works for me. Maybe you did not change the textbox name to Textbox or your sheet Logbook does not exist.

Did you try the ControlSource property?

 

[borreltijd]

Not sure what doesn't work means. It works for me. Maybe you did not change the textbox name to Textbox or your sheet Logbook does not exist.

Did you try the ControlSource property?

As you can see in the macro the name of TextBox is correct, I also checked the name of the sheet LogBook.

I have uploaded a excel sheet to my server:

www.dsvd5.nl/TmpStorage/savetext.xls

If somebody could look into the module which contains the sub Savetext() and fixed the problem I would be thankful !

 

[Kenneth Hobson]

I see the problem now. I did not read your first post well enough to see that you were using an autoshape textbox.

Sub SaveText()
  ActiveSheet.Shapes("TextBox").Select
  Worksheets("LogBook").Range("A1").Value = Selection.Characters.Text
  ActiveCell.Select
End Sub

I prefer the Textbox control in the Control Toolbox toolbar myself.

 

[borreltijd]

I see the problem now. I did not read your first post well enough to see that you were using an autoshape textbox.

Sub SaveText()
  ActiveSheet.Shapes("TextBox").Select
  Worksheets("LogBook").Range("A1").Value = Selection.Characters.Text
  ActiveCell.Select
End Sub

I prefer the Textbox control in the Control Toolbox toolbar myself.

Hi,

this works, thankyou for that.

No I have another small curious failure when saving the data. Take a look at the following code:

Sub NameInput()

Name = InputBox("Please enter your name", "Input data", Default:="User name")
Name = "User: " & Name
MsgBox (Name)

End Sub



Sub TextBoxSave()

Number = 1

Do Until IsEmpty(Worksheets("LogBook").Range("A" & Number)) = True
Number = Number + 1

Loop

Worksheets("LogBook").Range("A" & Number).Value = Date
Worksheets("LogBook").Range("B" & Number).Value = Time
Worksheets("LogBook").Range("C" & Number).Value = Name

ActiveSheet.Shapes("TextBox").Select
Worksheets("LogBook").Range("D" & Number).Value = Selection.Characters.Text
  
MsgBox ("Saved !!")

End Sub

The TextBoxSave() procedure works fine now. As you can see the procedure finds the first empty row in the LogBook sheet. Then it write the data, time, username and comments from the textbox to the sheet.

The problem I have now is about saving the user name.

If I user the NameInput() procedure this works. The message box perfectly returns ' user: Donald Duck'. But when I write it to a cell with the TextSave() procedure it writes nothing. When I delete: Name = "User: " & Name it works but then it doesnt add the ' User: ' string.

You now what I mean....


PS:
What do you mean with: I prefer the Textbox control in the Control Toolbox toolbar myself. Would it be better / more appropriate ?

 

[borreltijd]

I found the problem. I had to make the 'Name' a public variable, lol

I am only used to program matlab code, so this is quite new to me. !

 

[borreltijd]

Hi,
PS:
What do you mean with: I prefer the Textbox control in the Control Toolbox toolbar myself. Would it be better / more appropriate ?

Hi,

I found what you mean with control toolbox. That way I can really embed this textbox in my excel sheet. I am working on that one right now !