You are here:

Excel/vba userform output


Mr. Ogilvy,

Thank you for your prior answers. I had a problem with your last solution. When I type in your solution there was something wrong with line set tb= me.controls(stb). Not sure what I have type wrong. (I did add I=16 to begin with box 16. I think this is correct)

Dim i As Long, STB As String, SRANGE As String
Dim r As Range, tb As MSForms.TextBox
i = 16
For i = i To 21
STB = "texbox" & i
sRng = "data" & i
Set r = Range(sRng)
Set tb = Me.Controls(STB)
On Error Resume Next
If Len(Trim(tb.Value)) <> 0 Then
r.Value = Trim(tb.Value) * 1
End If
On Error GoTo 0
End Sub

Also you stated you did understand when I said I did not want zero or blanks. I  said that wrong I did want either zero or blank

Finally is there a way to control the input so that it is restricted to only text or only numeric?

If the code is in the userform, then "me" is a predefined reference to the userform that contains the code.

If you put the code someplace else, then you would have to reference the userform with the textboxes.  instead of me then  Userforms("MyUserform").Controls(STB)

Another error might be that STB doesn't contain a valid textbox name.  I don't know how your textboxes are named - I was going with what you told me.  However, I would not use a construct like

i = 16
For i = i to 21.  It may work, but it seems weird.

I would use
For i = 16 to 21

or if you must have a variable

ii = 16
for i = ii to 21


I tested the code I posted (altered for two textboxes) and it worked fine for me.  

Beyond that, I can't debut your implementation of the code I suggested.  As I stated, it was tested in my environment and it worked for me.

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.