Textbox in UserForm - It will not let me input data...

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518
This is my very first posting on Mr Excel, so bear with me.
I am a VBA beginner, but a quick learner.

1) I created UserForm1, I open with a Button in Sheet1 using
UserForm1.Show
With no other code in this macro.

2) I created a textbox in UserForm1, named: Username

3) I created a button in UserForm1, named OKButton , its unfinished code:

****
Private Sub OKButton_Click()

' Make sure Sheet1 is active
Sheets("Sheet1").Activate

'
' ?? Code to place the text typed in textbox to Range("A1") ??
'

' Clear the controls for next entry
' Got code below from a snippet on Mr. Excel
'
Username.Text = ""
OptionUnknown = True
TextName.SetFocus

' Close the User Form, can reopen with command button
Unload UserForm1

End Sub
****

Once I show UserForm1 via button in Sheet1 I see the textbox and the OK button - Good.
But, I can not enter data into the textbox, Its as if it is a locked cell - Bad.
When I try the OK button it runs the code until it hits the Username.Text = "" code. Actually, if I type in 'Username.' in the editor, my dropdown list does not show 'Text' as an choice after the period following the texbox name. Thus, if I try: Username.Text = "" or Range("A1") = Username.Text the use of Username .Text seems acceptable to VBA.

NOTE: I have no sub nor private sub for Username textbox.

I don't know much about onfocus, etc. If you show an example be clear when you are being literal vs. replace this word with ... your code here.

Actually, I also need the textbox entry (on OK) to be assigned as a string variable that will then immediately autorun in a macro named Find_N() in Module 1. (ie: I need to know how to call macro and place textbox entry as a string variable.)

Hints on that may reduce some followups.
You can email me along with post. :coffee:
Working late tonight - Thanks, TTom
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518
Username.Text is 'unacceptable' in code
I wrote 'acceptable'
TTom
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
How about something like this?:
Code:
Private Sub OKButton_Click()
' Make sure Sheet1 is active
'No need to do that.  We specify Sheet1 here
'Place the text typed in textbox to Range("A1")
Worksheets("Sheet1").Range("A1").Value = Username.Text
' Clear the controls for next entry
' Got code below from a snippet on Mr. Excel
' What is "OptionUnknown = True"?
Username.Text = ""
Username.SetFocus

' Close the User Form, can reopen with command button
Unload UserForm1

End Sub

I've modified your original code, and put a few comments in to explain. Please post back if you don't understand something I added or omitted. Hope that helps!
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!
I can not enter data into the textbox, Its as if it is a locked cell - Bad.
I see that Taz has you covered on most of it, but for that part check the UserForm Locked property-->it's likely that you have it set to True; for user entry it needs to be False. If you can't see the properties window when you're in VBA, hit F4.

Hope that helps,

Smitty
 

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518

ADVERTISEMENT

Taz, Smitty, Thanks for your replies!

1) I am using Excel 2002 on Win XP
2) Tried your code, I got - Runtime Error 438 : Object does not support property or method on code line:
Worksheets("Sheet1").Range("A1").Value = Username.Text
3) Again, seems to be line using: .Text ?
4) I looked at properties for userform - did not see 'Locked' Guessed at Enable - was set True. Toggled to False and could not access 'any part' of userform. Set back to True.

Other ideas? Thanks again. TTom
 

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518
I went through the suggested tutorial link and did the spreadsheet with userform. Always willing to study and learn. That program worked nicely. I need to study it and compare with mine to find out what is different in mine that doesn't work!
I'll post an update if I find the solution to my problem. Drop a note if you see anything else from my notes that might make mine hang.
Thanks again. TTom.
 

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518
I could not get form to work with .Text in code; however, after study of suggested link I now have a working code with a few addtions :coffee:

Smitty, thanks for the help and followup! (y)

I can now open userform, enter Username & Password, click OK will send the data to first row of DB1 sheet then clear and close userform. One can close userform with Close button but not by 'x' in box corner.

I have two sheets named: Setup & DB1
I named my Userform: frmLogin
I have button on Setup sheet using macro in Module1 to display userform:

' Marco to display Userform (named: frmLogin)
Sub Button1_Click()
frmLogin.Show
End Sub

I have two textbox in user form named: txtUsername & txtPassword
I have two buttons in userform named: cmdOK & cmdClose
I set the cmdOK property for default as TRUE
This is the code for the userform:

' Macro for OK Button in Userform
Private Sub cmdOK_Click()
Dim ws As Worksheet
Set ws = Worksheets("Setup")
Set db1 = Worksheets("DB1")

'check for a Username textbox entry
If Trim(Me.txtUsername.Value) = "" Then
Me.txtUsername.SetFocus
MsgBox "Please enter your Username"
Exit Sub
End If

'copy the textbox data to Sheet named DB1, Row 1, Cols 1 & 2
db1.Cells(1, 1).Value = Me.txtUsername.Value
db1.Cells(1, 2).Value = Me.txtPassword.Value


'clear the data in textbox
Me.txtUsername.Value = ""
Me.txtPassword.Value = ""
Me.txtUsername.SetFocus

'close the form
Unload Me

End Sub


'Macro for Close Button in Userform
Private Sub cmdClose_Click()
'Close the form
Unload Me
End Sub


'Macro to not allow close by 'x' in box corner
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
'Don't use the 'x' to close
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub

:eek: Hope others can use this as a start point as I am. TTom
 

Forum statistics

Threads
1,148,216
Messages
5,745,440
Members
423,952
Latest member
EduardoM

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