VBA Excel User Form Output to Txt File

3.6k views Asked by At

I am trying to create a user form that takes user input and prints to a text file. I know the VBA scripting functions to print, store strings etc. BUT I'm new to userforms. How do you limit input and output to a text file? Add to that same file?

INPUT AND OUTPUT:

inputandoutput

I would need to have this variety of

KEY FEATURES:

  1. Limited Entry: XXXX means only enter 4 characters (The notepad file can only be certain characters long

  2. Multiple Entries: After a set of records is entered to the text file, their values are stored and another input is allowed. Also the new entries have to be written to the same file on the next line

  3. Space if left blank: If XXXX is left blank then four " " should be printed instead.

If you have part or all of these answers I'd like to hear from you!

---------------------------Edited with Adding Code-----------------------------

Private Sub Userform_Initialize()

'Fill Eggs
With Eggs
    .AddItem "Eggs"
End With

End Sub

Private Sub CreateList_Click()

Dim myFile As String, myString As String
myFile = "C:\Reformatted.txt"
Open myFile For Output As #1

Dim fourChars As String * 4
fourChars = Milk.Value

myString = Eggs.Value + Milk.Value + Bread.Value

Print #1, myString

Close #1
Shell "C:\Windows\Notepad.exe C:\Reformatted.txt", 1

End Sub

So the above code launches this screen:

1

There are 1 answers

4
L42 On BEST ANSWER

TextBox have MaxLength property. Default is set to 0 which does not limit character entry.
To limit the entry into a certain number, just change the value of this property to that number.

sample

Now for replacing the XXXX with blank(if none or less than 4 is entered), take on SO advise in the comments.

Dim fourChars As String * 4
fourChars = TextBox1.Value
MsgBox Len(fourChars) ' will always return 4