Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Userform Text Boxes And "Proper"

I have a userform with text boxes that the user inputs Surname & Initial into. The VBA code then copies that information onto a worksheet. Is there anyway to include into the code, that would change the case of the inputted text to "Proper" before entering it onto the sheet?

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
UserForm Text Boxes & Dates
I have a UserForm with several text boxes where dates are input, the user form is acts as a data entry form so the text boxes are not linked to individual cells. My problem is that when the dates are entered into my worksheet they are in Text Format even though the worksheet column is formatted to Custom Date dd,mm,yyyy.

My code is as follows:-

Private Sub TextD_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Len(TextD.Value) = 0 Then Goto zExit

View Replies!   View Related
Set Date Through Calendar In The Userform Text Boxes
I have a userform and a calendar form, on the user form I have three text boxes named (txt1, txt2, txt3) and three buttons named (cmd1, cmd2, cmd3).

when I click I cmd1 button the calendar form opens and after selecting the Date from calendar, the value of the calendar assign in txt1.

the same for cmd2, cmd3

View Replies!   View Related
Check UserForm Text Boxes For Entries
I have a user form with 10 textboxes on and when you press the command button, VBA will send the values in each text box to a cell on the sheet. If a user only wants to enter one value, it zeroes all the others in that row!! Does anyone have any ideas please in very very basic lamens code!! It is only relating to the user form called "Figures1". I had an if command running in the "decweek1" modules, but it makes my procedure too long to do the same with the other one!

If you look at my code, you can see how unadvanced I am with this so really simple code would be gratefully received!! If anyone feels nice enough you could always write the code for this problem for me and send me back the workbook, however I don't expect anyone to do this!

It is too large to attach so it is here:-


View Replies!   View Related
Proper Case Text By Condition
How can I extend proper() to NOT change "PO Box 333" to "Po Box 333". Ideally, I would like to supply a list of words such as PO and all the 2 letter directionals (NE,NW,SE,SW).

There are also cases such as a last name of MacNamara which should have a capital M and N. Even worse, I see that 3rd becomes 3Rd which is very sad.

I'm assuming the data was supplied in uppercase

View Replies!   View Related
Change Text To Upper, Lower Or Proper Case On Entry
I would like to format a row of cells so that when a word is entered into the cell it automatically becomes a capital.

I need the word to be capitalized so that I can use it in a custom function. The function uses the word from this cell and goes through a bunch of cases in determing how to classify the string.

I think more than one solution is possible and I would greatly appreciate some feed back, I've tried looking into turning all the letters of a string in my VBA code to capitals, or a way to format the cells, so that the string is already capitalized when entered into the VBA code, but I'm still a novice at VBA and unsure on how certain commands work.

here is a sample of my vba code.

Function WeightI(Shape As String, sDim As String, dLenFt As Double) As Double
Const pi As Double = 3.14159265358979
Const Ft2In As Double = 12
Const dDen As Double = 0.2835 ' density of steel, pounds per cubic inch

Dim aiStr() As String ' dimensions as strings

View Replies!   View Related
Combo Boxes In UserForm
I created a combobox on the fmEdit userform that I want to use to select a name from the worksheet and display on the Userform the data from that row for editing, which can then be submitted to make changes accordingly on that row.

View Replies!   View Related
Userform Create Input Boxes
How would I go about making a user form that has an input box where you can type in a year (in yyyy format), and then two radio buttons that allow you to choose either "Jan-Jun" or "Jul-Dec", and a submit button to run a corresponding macro.

The purpose of this is I have two macros that run the same report, one is for first half of the year, second is for second half of the year. For simple purposes we will call them "JanJun" macro and "JulDec" macro ...

View Replies!   View Related
Add Data With Check Boxes In Userform
What Im trying to do add some addition data to a spreadsheet, I think a userform is the best way to do this. Worksheet = sheet 1 (Current Data)

I have an Item number in column C of sheet 1 (currently 570 items)
I have a spot for the values of my checkboxes on sheet1

What I would like is to be able to select the item number from a combo box (cmbITEM) And check off all or some of the checkboxes (I have 12 checkboxes = chk01 through chk12) then have that update the data on sheet1

View Replies!   View Related
UserForm To Select Pages To Print Using Check Boxes
I have a UserForm that I want to use as a “Print Selection Page” using check boxes to make selection or multiple selections of pages to print from different spreadsheets but same work book.

View Replies!   View Related
Find A Text Or Number In Text Boxes
I have a couple of excelfiles in which someone made a flowchart with text boxes. I would like a search code that would help me find a text or number in those text boxes. The CTRL+F function only works on cells. Does something similar exist for text boxes? Or can this be made in a macro?

View Replies!   View Related
Replace Text In Mutiple Text Boxes
Trying to Find a specific word and Replace with another within a textbox ( created from the drawing tool, as well as the control toolbar) The textboxes contain loads of text information.

as an example
The word "Apple" to be replaced with "Orange"

I came across this code from the archives (compliments of Dave Hawley)

Sub ReplaceTextBoxText()
Dim sTextBox As Shape
Dim wSheet As Worksheet
Set wSheet = Sheets.Add()
For Each sTextBox In Sheet1.Shapes

I've tried several variations with no results. (perhaps it's to extensive for my modest needs)

View Replies!   View Related
UserForm Text Box Code (whatever Text Has Been Entered Into The Userform To Be Put Into A Cell )
I've created a userform with a text box and a command button (Enter)

On clicking the command button I'd like whatever text has been entered into the userform to be put into a cell (Say A1).

View Replies!   View Related
Many Multiple Text Boxes
I have 10 Text boxes each, on many tabs of a Multipage. I have a Private Sub LoadRow() for one tab.Is there a way to use a Sub that would work for each tab or do I have to write a new sub for each tab? Here’s the sub I’m using.
Private Sub LoadRow()

txtTrussco.Text = Cells(lCurrentRow, 1).Value
txtTPhone.Text = Cells(lCurrentRow, 2).Value
txtTFax.Text = Cells(lCurrentRow, 3).Value
txtTAddress.Text = Cells(lCurrentRow, 4).Value
txtTCity.Text = Cells(lCurrentRow, 5).Value
txtTState.Text = Cells(lCurrentRow, 6).Value
txtTZipcode.Text = Cells(lCurrentRow, 7).Value
txtTSalesrep.Text = Cells(lCurrentRow, 8).Value
txtTSrepphone.Text = Cells(lCurrentRow, 9).Value
txtTrusseng.Text = Cells(lCurrentRow, 10).Value

End Sub

View Replies!   View Related
New Tabs Using Text Boxes
I m creating new tabs that users input into a text box and two label: one each becuase one tab will be Tab1 then then other label will be Tab1 Completed.

How do you continously update the label so it changes as the user inputs letters.
I never used text boxs before so thus another reason I have no clue.

View Replies!   View Related
Tab Order Of Text Boxes
I have been working on an excel sheet with several text boxes that need data manually putting into them. I have set the texts boxes out in the correct order and could tab thru them perfectly in order, but then I saved, closed then reopen the document and now the text boxes do not tab in order. I have tried to rearrange them, delete them and start again but the same problem happens every time and I cannot get the tab order of the text boxes to stay in order

View Replies!   View Related
Tabbing Through Text Boxes
I may be doing this wrong, but I have a form that i created using Active X controls in Excel 2007 (I couldn't use the form control text box, it was greyed out). I have textbox1, textbox2 etc... I got the form to work but I can't tab through. When the user is done entering data in textbox1, I want to set the focus to textbox2. i would like this to happen with enter and tab.

View Replies!   View Related
Locking A Certain Text Boxes, And Not Others
For example:

Locking a certain text boxes, and not others. Under review, protect sheet, the Edit Objects function is all or nothing

View Replies!   View Related
ENABLE Text Boxes
I have a ton of text boxes on a userform that are in an initial state of being disabled. Is there a quick way of enabling by use of a mask for these Text Boxes ?

All the text boxes I want to enable are named "TB_fieldname" and by mask I mean a snippet of code using something like "TB_*"

tb_liqname, tb_casecost, tb_bottleprice etc etc..

View Replies!   View Related
VLOOKUP In VBA - Text Boxes
Am trying to use a VLOOKUP in VBA on a form, I am entering data in one field so data will be displayed in another field. They are both text boxes. Here is the code;

View Replies!   View Related
Convert A Text Date Into A Proper Date
I have a column of dates in thie format " January 5 03:09:36 2009" which i need to convert into a proper excel date that i can do further calculations on (adding up things, graphs etc based on dates).

Note the space before the month name. I thought about doing a left(cell,xx) to get it but that isnt going to work with the space infront and the variable length of month names.

I do not necessarily need the time included, it may be useful at some point if its easy to keep it as part of the data, if not i can live without it.

View Replies!   View Related
Transferring Text Boxes To Cells
I want to transfer the contents of around 20 textboxes to 20 different cells. Allthough I have seen many code examples for doing this with a single text box e.g.

Sub getext()
Dim s As String
ActiveSheet.Shapes("Text Box 1").Select
s = Selection.Characters.Text
Range("A100").Value = s
End Sub

I can't seem to get it to loop through 20 boxes automatically using a For next loop for instance. Also I would like it to skip any empty text boxes it finds.
From what I've seen I don't think it would take too much code but I'm afraid I'm a complete beginner with macro's and completely stuck.

View Replies!   View Related
Change Text Boxes In Do While Loop
I made up a form of 30 text boxes to simplify entering data, but seem to be having a problem inserting it easily back into my excel file.

I would like to use a simple Do While loop to shorten my code but can't seem to change my text box correctly.

My text boxes all have names like this txtap1run11, txtap1run12, txtap1run13...

As you can see all I need to do is change the last number of the text box name.

I can't figure out how to do that though. I came up with this, I don't have the do while loop in.

View Replies!   View Related
Select/Delete Worksheet Text Boxes Using VBA ...
I'm sure you're all familiar with Text Boxes:

You can add shapes and text boxes (text box: A movable, resizable container for text or graphics. Use text boxes to position several blocks of text on a page or to give text a different orientation from other text in the document.) to a worksheet by using the Drawing toolbar.

Did you know that if you click the text box button, then click on a spreadsheet (inserting a blank text box), then click off the text box, a small blank text box will remain hidden from view on your spreadsheet? Once hidden, the only way to find it is to slowly move your cursor over the Text Box edge, and watch the cursor momentarily change shape. Did you also know that if you copy or fill down the cells behind that text box, it will make as many copies of itself as you made for the cell? Not too dis-similar from a virus.

Imagine what would happen if you unknowingly had some of these text boxes on your main calculation sheet (200 columns by 2000 rows); one where you regularly copied formulae/cells for a period of over four years. Yes, (judging by their names e.g. "Text Box 29413") the thriving population of these invisible beasties is now in the neighbourhood of thirty thousand! The damn things are worse than fleas; and they're impairing the function of the whole workbook.

Help! Would someone please provide a VBA macro to find, select and delete these little devils from the spreadsheet (just this one spreadsheet)?

View Replies!   View Related
Calculating An Average But Excluding Boxes With Text
I'm trying to get excel to calculate the average time spent on appointments by officers in a small team. Each of them sees people each day, and as we're very busy I'm eager to get the time spent on each appointment down, by giving them a target of the number of people to see each day. At the moment we gather some stats on this, which show how many people are being seen by each officer.

The attached example sheet shows for one week a list of columns with officer's initials at the top, and says how many people were seen by each person each day during that week and then a how many people overall for the week.

I'd like to be able to generate statistics on the amount of time each officer has spent on average with customers, and base this on the fact that people have 360 minutes per day (they do 6 hours of appointments and another hour for admin) to see people.

I think the spreadsheet attached gives a good idea of the system and what I want to do.

View Replies!   View Related
How Text Boxes Work In Conjunction With CommandButton
I am trying to make a calculator inside of Excel...learning how Text Boxes work in conjunction with CommandButton. I am trying to code the button to display inside of the text box. I have never coded a text box before to do anything like.

note: For right now i am just looking at being able to click on a button [numbered 0-9] and have them dsplayed inside the text box. After that I want to be able to set up an addition, subtraction, etc button to actually have the math done.
(ie...if Button 1 is clicked, 1 will be displayed, if Button 2 is clicked the display will adjust to show 12, etc etc)

View Replies!   View Related
Controlling Multiple Text Boxes On A Form
I have a userform with 40 text boxes in it. I have learnt how to restrict people entering anything other than numbers into a text box using the following code on the keypress property of the textbox:

Const Number$ = "0123456789." ' only allow these characters

If KeyAscii 8 Then
If InStr(Number$, Chr(KeyAscii)) = 0 Then
KeyAscii = 0
Exit Sub
End If
End If

Rather than have 40 of these blocks of code (one for each text box), is it possible to somehow group them so that a block of code is applicable to all of them?

For the sake of arguement, the textboxes are called data1 up to data40

View Replies!   View Related
Text Inconsistencies In ActiveX Combo Boxes
I'm building an application that uses a large number of activex combo boxes. These boxes are getting loaded from specific fields in an associated database.

I know ActiveX combo boxes can be buggy; I'm wondering if anyone has found a way around the text appearance inconsistencies seen in these controls? e.g., sometimes the text is smaller, sometimes it's spaced wider, sometimes it appears at the top of the combo box sometimes in the center.

I put a screen shot of what I'm talking about in a word doc along with a control properties screen shot, All controls have the same properties except for the text property.

View Replies!   View Related
Measure The Length Of Text In 10 Boxes Consecutivly.
I am trying to use a For loop to measure the lenght of text in 10 text boxes on a user form so I can run a check but can't think of a way to do it. This is the best I have so far...

View Replies!   View Related
Changing The Property Of Multiple Text Boxes
I have a userform within an Add-in. The form contains multiple text boxes, shown as a grid. When the user uses the calculate button - the text box in question is raised and highlighted using the follwing code within a case statement:

View Replies!   View Related
Verifying Multiple Text Boxes Are Not Empty
I have many text boxes on a form and if any of them are empty then I want a msgbox to popup and exit sub. I know how to do the following

If txtExample1 is "" then msgbox and exit sub
if txt....2
if txt...3

Is there anyway to group the text boxes to test for empty so you don't have to list 20 textboxes

View Replies!   View Related
Delete All Text Boxes In A Spreadsheet/Workbook
I have a spreadsheet with over 20 tabs - each containing 6 text boxes containing financial commentary. Each month, the tabs are 'overpasted' with the latest month equivalents. This results in the latest month's text boxes sitting on top of the previous month's. I would like to attach functionality that deletes all the text boxes at the start of the monthly cycle - so the 'overpasting' exercise starts with a spreadsheet that contains no text boxes. I have been unsuccessfull with my attempts because each text box has a unique number - which changes every month. Does any VBA script exist to delete all the text boxes in a workbook or tab?

For reasons that are too longwinded to go into, there is no way around the overpasting set-up as summarised above (I recognise this is far from the ideal solution).

View Replies!   View Related
Display Currency Format With Text Boxes
What I am trying to do is display my output in currency format. The catch is that the values I am using are not from cells, they are from text boxes that are locked on a user form. The first text box is a base rate with a value of "$2000.00". Second comes a quantity text box with an integer value of let's say "3". Last comes a markup rate which is a text box with a rate of ".10". The calculation is fine, it although it returns "$6600" when it is critical that I display the two decimal places at the end. Here is my code for the calculation:

Me.txtAirfare.Text = "$" & frmAirfare.txtBaseRate.Value * frmAirfare.txtQuantity.Value * (1 + frmAirfare.txtMarkup.Value)

View Replies!   View Related
User Form Basics - Populating Text Boxes
I am trying to create a user form to edit some named ranges. The VBA user form designer is basically the same as Borland Delphi, so building the form itself was easy. What I can't figure out is how to populate the user form with the data from the spreadsheet.

I have a number of text boxes on the form with names like txtTier1Slots, txtTier1Ceiling, txtTier1Floor, etc up to 4.

I have a defined name that corresponds with each item, Tier1Slots, Tier1Ceiling, Tier1Floor, and so on. I'm using defined names because they're referenced in Conditional Formatting on one of the worksheets in my project. They don't exist as actual cells, just name references.

I want to get the form populated with those values. I've tried about twenty variations of the following code, using ThisWorkbook, Workbooks, Cells, Range, Name, and just about every cell reference method I can think of. I've so many different error messages, my head is spinning.

View Replies!   View Related
Populate Two Text Boxes Based On A Combo Box Selection
I want to populate the values of two textboxes that we will call TextBox5 and TextBox6 from columns W and X of a spreadsheet called "PowerAnalysis" when a selection is made form ComboBox5.

The ComboBox5 works perfectly now. I just need it to populate the other two text boxes.

All the data resides in the same row on the same sheet of PowerAnalysis when the selection is made in ComboBox5.

I hope I have been able to give a clear picture of what I am wanting to do.

View Replies!   View Related
Percentage Formats In User Form Text Boxes
I have a user form which includes some text boxes (txtRUL.text for example) where I want to enter a number as a percentage. I divide the number entered by 100 to avoid Excel automatically multiplying by 100 and the result is displayed in the user form as the correct percentage.

Private Sub txtRUL_AfterUpdate()
txtRUL.Value = Format(txtRUL.Value / 100, "0.0%")

(I'm sorry I cant figure out how to format this code as code in this post)

This works well, trouble is when I save and it writes the results to the worksheet, the numbers in the user form revert back to plain unformatted non percentage numbers (ie 5% goes to 0.05)

View Replies!   View Related
Detect Duplicates In User Form Text Boxes
I have a user form with 4 text boxes and a command button. I have no problem looping through the textboxes to retrieve the value, but what is the best way to detect if any duplicates exist between the four text boxes? For example, in the command button's
If textbox one's value is "test" and textbox four's value is also "test", it should throw an exception. If a question like this has already been answered, a link to that post would surely suffice.

View Replies!   View Related
Populate Listbox & Text Boxes From Data In Sheet
I have a userform with a multi select listbox and 7 textboxes and a sheet with all the data on called "Metdata"

See "Metadata" sheet data below:

I want to:

1. Populate the Listbox1 with the data from column A, which starting at cell A3 and down until cell/row is blank. In the example "Metadata" sheet below I only two rows are present but that will increase to 200+ rows.

listbox1 = data from column A starting A3.

2. When the user selects a single item in the listbox1, I want the 6 textboxes to be populated with the data from the other columns related to the row selected as follows:

textbox1 = column B - starting cell B3
textbox2 = column C - starting cell B3
textbox3 = column D - starting cell B3
textbox4 = column E - starting cell B3
textbox5 = column F - starting cell B3
textbox6 = column G - starting cell B3

Every time the user changes the item selected in the listbox1, I want the textboxes to be populated with the data from the corresponding row selected.

3. When the user selects more that one item from the listbox I want all the textboxes to be locked = true and textbox7 = "Multiple files Selected"

Obviously when a single selection is made from listbox1 that all textboxes are unlocked for use....

View Replies!   View Related
Propagating Text Boxes Dependant On Cell Relationship
I have been working for three straight days (and nights!) to accomplish something that is very simple in other languages, but I can't make it work in VB

Column X has, with spaces between 4 possible contents:

St1 St3
3Q today
FM today
1Q today
NM today
3Q today

and so on for 1500 rows.

I can write this in Lotus in about 2 minutes, but VB has me stumped. I hope someone can put me on the corrrect path.

View Replies!   View Related
Select/Delete Worksheet Text Boxes Using VBA On Active Sheet
I could use some assistance in creating a macro that will delete all and only text boxes on the active sheet. Some text boxes will be empty, but I still wish for them all to be deleted. Through searching the forums I see a similiar code, but it deletes all shapes on the active sheet:

View Replies!   View Related
User Forms Basics - Text Boxes To Named Range
User Form Basics - Populating Text Boxes. A few tweaks later and its reading the data just fine. Some of the specific form objects and range names have changed, but it's the same logic.

Anyway, this isn't just for display. I need to export the data back to the spreadsheet. So, I plugged this line into the event handler for the "Save & Exit" button on the sample form in the other thread:

View Replies!   View Related
List Boxes, Combo Boxes???
I am developing a spreadsheet with numerous information on our different suppliers offices in the country for each of our outlets. I am tryin to define a way to do the following:

If a colleague selects a company another list will appear with the region and when the region is selected a list of the offices will appear.

View Replies!   View Related
Proper Filtering
Is there a way to filter my data with the combobox and have the listbox reference the filtered data directly?

I am populating a listbox from a rowsource, housed in a sheet in my workbook, the sheet in my workbook is populated from another sheet that is filtered by a combobox selection and pasted to the sheet with the rowsource range.

My listbox is a multiselect with 5 colums and columnheads, named ListBox42

The sheet with the data to be filtered is named Building Material

Building Material is filtered in column “M” with a color

With ComboBox24 selected name: Names “M1” which activates the color down the column for filtering.

The ComboBox24
Private Sub ComboBox24_Change()

Worksheets("BuildingMaterial").Range("M1").Value = ComboBox24.Value
ActiveSheet.Range("$M$1:$M$4247").AutoFilter Field:=1, Criteria1:=RGB(0, _
176, 240), Operator:=xlFilterCellColor
ActiveSheet.Range("$M$1:$M$4247").AutoFilter Field:=1
Application.CutCopyMode = False
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst

End Sub

My RowSource is a defined name “=matfilter!$A$2:$E$4230 “

View Replies!   View Related
Proper Protection
Is there any way I can protect my sheets properly..? I know you can use Tools > Protection.. but I've found (and used, for good, not evil!) macros on the web that will crack these in seconds. Is there any way I can disable the 'Tools' menu so that other users can't load these password crackers?

View Replies!   View Related
Display Text In Text Box In Userform
What code should I use to display a certain text (or number) in a text box in a UserForm?

View Replies!   View Related
Use Proper Case Only For Input Box
how can i change the text put into an input box into proper case regardless of what my user puts in?

View Replies!   View Related
Proper Case In A Macro
I have just recently found that I can do case correction with Excel but I am manually having to do it how can I add it to my macro? The function for doing it does not seem straight forward to me on putting in macro I am sure it is simple but just missing some element of it.

I need to have Proper case for columns C, G and H from rows 11 and down.

View Replies!   View Related
Change To Proper Case In VBA
I found this bit of Worksheet_Change code to change the target area to UpperCase. This works fine.

If Not Intersect(Target, Columns(2)) Is Nothing Then
Set rng1 = Intersect(Target, Columns(2))
Set rng2 = Intersect(ActiveSheet.UsedRange, rng1)
For Each cell In rng2
If cell.Formula "" Then
cell.Formula = Format(cell.Formula, ">")
End If
Next cell
End If
I could not find anything telling me what the ">" means. I'm assuming that it is a special symbol/wildcard for UCase in VBA.

My question(s), is there a symbol for ProperCase so I can use the same code, just making it change the Target column to Proper? Also is there a list of the special symbols.

View Replies!   View Related
Proper Case Exemptions
When I asked this question before, I was looking for a way to automate the exemptions on a UserForm. At that time I realized that automation was not a good choice and went with a CommandButton to turn off the Proper case for that entry. I am now trying to do the same thing on a Worksheet change event using this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column < 1 Or Target.Column > 9 Then Exit Sub
On Error Goto Errhndl
Application.EnableEvents = False
If Target.Column = 1 Then Target.Value = Application.Proper(Target.Value)
If Target.Column = 2 Then Target.Value = Application.Proper(Target.Value)
If Target.Column = 3 Then Target.Value = UCase(Target.Value)
If Target.Column = 4 And Target.Value > "" Then Target.Value = UCase(Left(Target.Value, 2)) & "-" & Right(Target.Value, 2)
If Target.Column = 8 Then Target.Value = UCase(Target.Value)
If Target.Column = 9 Then Target.Value = UCase(Target.Value)
Application.EnableEvents = True
Exit Sub
Application.EnableEvents = True
End Sub

My problem is with Target.Column = 1. I need a way to disable the proper case for a single row. I tried to use an additional column (J) and place a x in that row, but I could not figure out how to detect if there was anything in that column for the target row.

View Replies!   View Related
Converting Worksheet To Proper-Case
I have an Excel 2003 worksheet with all the data in it in Upper-case.

I found this VBA code which works if you change a cell.

View Replies!   View Related
Copyright © 2005-08, All rights reserved