Solved

Upper case in text box

Posted on 2010-09-14
8
720 Views
Last Modified: 2012-05-10


Private Sub Calendar1_Click()

TextBox1.Text = Format(Calendar1.Value, "mm/dd/yy")
    'inserts date into textbox1


End Sub

Private Sub CkBoxIncoming_Click()

End Sub

Private Sub CmdBtnCloseForm_Click()

Unload Me        'removes form from spreadsheet with cmd btn.

End Sub

Private Sub CommandButton1_Click()

    'inserts textbox data into spreadsheet fields.

Dim r As Range
Set r = Range("A40").End(xlUp)      'a65536
Set r = r.Offset(1, 0)
r.Value = TextBox1.Value
Set r = r.Offset(0, 1) 'moves right with each "1".
r.Value = TextBox2.Value
Set r = r.Offset(0, 1)
r.Value = TextBox3.Value
Set r = r.Offset(0, 1)
r.Value = TextBox4.Value
Set r = r.Offset(0, 1)
r.Value = CkBoxIncoming.Value

How do I force capital letters into the above text boxes?

How do I write the code in a better fashion with all the value lines?  e.g. with or select?
0
Comment
Question by:1r3o2n8
  • 5
  • 3
8 Comments
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33675563

Private Sub Calendar1_Click()

TextBox1.Text = Format(Calendar1.Value, "mm/dd/yy")
    'inserts date into textbox1


End Sub

Private Sub CkBoxIncoming_Click()

End Sub

Private Sub CmdBtnCloseForm_Click()

Unload Me        'removes form from spreadsheet with cmd btn.

End Sub

Private Sub CommandButton1_Click()

    'inserts textbox data into spreadsheet fields.

Dim r As Range
Set r = Range("A40").End(xlUp)      'a65536
Set r = r.Offset(1, 0)
r.Value = ucase(TextBox1.Value)
Set r = r.Offset(0, 1) 'moves right with each "1".
r.Value = ucase(TextBox2.Value)
Set r = r.Offset(0, 1)
r.Value = ucase(TextBox3.Value)
Set r = r.Offset(0, 1)
r.Value = ucase(TextBox4.Value)
Set r = r.Offset(0, 1)
r.Value = CkBoxIncoming.Value

Open in new window

0
 

Author Comment

by:1r3o2n8
ID: 33675816
That works great.  Thanks alot.  
In that area of code where you made the ucase corrections, is there a way to recode it, perhaps with a "with" statement, that will reduce the amount of code?
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33675934
No but you can do this which is even more efficient
on error resume next
Dim r As Range
dim ws as worksheet
set ws = activesheet
Set r = ws.Range("A" & ws.Range("A40").End(xlUp).row + 1 & ":E" & ws.Range("A40").End(xlUp).row + 1)      'a65536  
for each c in r
c.Value = ucase(me.controls("TextBox" & c.column).value)
next

Open in new window

0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33675969
Actually use this, don't use A40
on error resume next  
Dim r As Range  
dim ws as worksheet  
set ws = activesheet  
Set r = ws.Range("A" & ws.Range("A" & ws.range("A:A").rows.count).End(xlUp).row + 1 & ":E" & ws.Range("A" & ws.range("A:A").rows.count).End(xlUp).row + 1)      'a65536    
for each c in r  
c.Value = ucase(me.controls("TextBox" & c.column).value)  
next

Open in new window

0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33676011
oops, I looked over your combobox.....check the below code of the fix.....
on error resume next    
Dim r As Range    
dim ws as worksheet    
set ws = activesheet    
Set r = ws.Range("A" & ws.Range("A" & ws.range("A:A").rows.count).End(xlUp).row + 1 & ":D" & ws.Range("A" & ws.range("A:A").rows.count).End(xlUp).row + 1)      'a65536      
for each c in r 
if c.column <> r.cells.count then  
c.Value = ucase(me.controls("TextBox" & c.column).value)
else
c.value = CkBoxIncoming.Value
end if
next

Open in new window

0
 

Author Comment

by:1r3o2n8
ID: 33676601
Your code did the job!  May I have an explanation of lines 5-8??
0
 
LVL 13

Accepted Solution

by:
MWGainesJR earned 125 total points
ID: 33677747
It sets r to the range that you are filling and then loops through each cell. Because each column is mapped to the textbox number (column 1 to textbox1 etc) it can set the values dynamically. Just be careful when adding and subtracting columns/textboxes and renaming textboxes. You'll have to edit code with it.
0
 

Author Closing Comment

by:1r3o2n8
ID: 33677819
THANX!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This collection of functions covers all the normal rounding methods of just about any numeric value.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question