Excel/vba userform output
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
On Error GoTo 0
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.
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