How to show a TextBox on Button click in Excel

Question

Hello,

I am trying to do a comparison of my events in Excel. I have added an Button and would like to have an text-box pops-up after we click on the button.

Later we copy/paste the event details in text-box and save it. I am using below code on button code but that did not seems to work.

Sub Button1_Click()
        Dim txtControl As TextBox
        Set txtControl = Controls.Add("txtcontrol.Text", "txtControl")
        txtControl.Top = 300
        txtControl.Left = 200
        txtControl.Visible = True
End Sub

Any help would be much appreciated.

Thanks in Advance!

Answer 1

I am under the impression that what you are looking for is a messageBox, not a textBox. The textBox does not "popup" it just lies there; it is a mere text-placeholder. In any case, your code is wrong and should trigger an error. If you don't get anything (even not an error), you might have to confirm whether this Sub is actually associated with the click event of the button (bear in mind that the name "Button1_Click()" does not imply an automatic association with the button).

If you want to want a textbox (placeholder), this is the code:

Sub Button1_Click()

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 10, 10, 50, 20).TextFrame.Characters.Text = "TextBox"

End Sub

If what you really want is a messagebox (popup), you should use this one:

Sub Button1_Click()

MsgBox "MessageBox"

End Sub

Answer 2

Thanks for the reply.

As I said, I need to have an text box (multi-line enable) where I can insert my System Event Detail. So text box is a good option so if anyone want to see what event it is, they just need to click on the button and see it in a text box.

I tried using Forms but it did not get saved. I am not sure whether I need an external data source to save the input, but if that is the case then that might not feasible for us as we just need a simple yet informative type of spreadsheet, where we can track all the actions and the events in result of those actions.

I have tried first code and it just opens the text box on my sheet and just sit there. It did not get away.

Or, with MsgBox if I can show multi-line event, that would be great. Here is the sample data which I want to show/save-in-textbox or whatever method you think is good.

Event Type:    Information
Event Source:    NTDS General
Event Category:    Field Engineering
Event ID:    1644
Date:        6/18/2013
Time:        4:32:22 AM
User:        POWER\administrator
Computer:    SERVER2K3
Description:
Internal event: A client issued a search operation with the following options.
 
Client:
172.168.4.181
Starting node:
DC=power,DC=com
Filter:
 ( & ( !  (cn=*GroupIDGlobalConfigurationSettings*) )  ( |  (objectClass=group)  (objectCategory=CN=ms-Exch-Dynamic-Distribution-List,CN=Schema,CN=Configuration,DC=power,DC=com) ) )  
Search scope:
subtree
Attribute selection:
name,cn,displayName,description,groupType,objectCategory,mail,mailNickname,modifyTimeStamp,managedBy,extensionData,objectClass,targetAddress,homeMDB
Server controls:
 
Visited entries:
11618
Returned entries:
38

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Answer 3

Yes, you can create multiple lines in a messageBox:

MsgBox "Event Type:    Information" & vbCrLf & "Event Source:   NTDS General" & vbCrLf & "Event Category:    Field Engineering"

But this is way to crappy and don't think that you want that.

Macros are just to perform actions on Excel (Word, etc.) with a mininum in-built GUI capabilities. What you want is a complex GUI element (for the in-built GUI in a macro, at least) and thus you have to design it yourself. Create a form (equivalently as you created the button) and design it as you wish (include textboxes, macros, etc.).