0

Hi I'm not sure if this is doable or not. I'm trying to get the value of an input box in my userform and display it in a MsgBox during the time that it was focused or clicked.

Is there a way to do this?

Say I have Textbox1 with a value of Apple and when I click that TextBox1 MsgBox will appear that will display the value of my TextBox1 which is Apple.

Note: I have a lot of TextBox so I'm not only referring to TextBox1.

Thanks for the help.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Brandon Jake Sullano
  • 335
  • 2
  • 7
  • 23
  • Refer to [this](https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.controls.textbox_events(v=vs.120).aspx). There are many Events that you can use: Click, Change, Exit, etc... – danieltakeshi Oct 24 '17 at 12:06

1 Answers1

1

You can either use the TextBox1_Enter() event (when the text box is entered) or the TextBox1_MouseUp event (when the text box is clicked) to trigger a message box and you will have to do this for each Textbox.

But you can use procedure:

Option Explicit

Private Sub ShowMyMsgBog(Value As String)
    MsgBox Value
End Sub

'you need one event for each TextBox
Private Sub TextBox1_Enter()
    ShowMyMsgBog TextBox1
End Sub

Private Sub TextBox2_Enter()
    ShowMyMsgBog TextBox2
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Hi I can't see code: 'Private Sub ShowMyMsgBog(Value As String) MsgBox Value End Sub" working. – Brandon Jake Sullano Oct 24 '17 at 12:11
  • is there a way to loop the Message box Enter? my Message box varies on the number of rows that has value on my cells – Brandon Jake Sullano Oct 24 '17 at 12:12
  • (1) I did not understand your first comment. If it is "not working" tell what error message you get. (2) No you need one event for each TextBox there is no shorter way. (3) I think my code does exactly what you described in your question. If not then you question was asked bad. – Pᴇʜ Oct 24 '17 at 12:16
  • I mean I copied your first code that determine if I click my textBox and no error but also no result. It did not show up MsgBox – Brandon Jake Sullano Oct 24 '17 at 12:18
  • My TextBox is under a userform – Brandon Jake Sullano Oct 24 '17 at 12:19
  • (1) Of course you need both to get it work. The `ShowMyMsgBog` **and** `TextBox1_Enter` is needed to show a messagebox! And for more TextBoxes you need a `_Enter` for each TextBox. (2) "*My TextBox is under a userform*" that's nice but doesn't change anything. – Pᴇʜ Oct 24 '17 at 12:20
  • Will review this code , also did not work in my side. I have my textbox created by below method – Brandon Jake Sullano Oct 24 '17 at 12:23
  • Set box = Me.Controls.Add("Forms.TextBox.1", row.row & colIndex) – Brandon Jake Sullano Oct 24 '17 at 12:23
  • I copied your code and don't give me the result. can you give me a sample file ? – Brandon Jake Sullano Oct 24 '17 at 12:24
  • 1
    So why didn't you tell us that you dynamically added the textboxes? That's of course a completely different approach! But this was already solved here: [How to add events to Controls created at runtime in Excel with VBA](https://stackoverflow.com/questions/3014421/how-to-add-events-to-controls-created-at-runtime-in-excel-with-vba). So have a look there. – Pᴇʜ Oct 24 '17 at 12:26
  • Thanks for that link will go back once I solve the problem using that reference its been 2 days searching for a solution . thanks bro. – Brandon Jake Sullano Oct 24 '17 at 12:28