Free Microsoft Excel 2013 Quick Reference

Create fields in a form with a fixed length

Is it possible to create fields in a form with a fixed length? I tried using
Data -> Validation. This does not work well because the user does not know
the length has been exceeded until exiting the field. For example, I would
like to set a field to a length of 20 characters and prevent the user from
entering any data past the 20th character. I can create borders for the
fields but Excel allows the users to keep typing, and they keep typing.


Post your answer or comment

comments powered by Disqus
Is it possible to create fields in a form with a fixed length? I tried using
Data -> Validation. This does not work well because the user does not know
the length has been exceeded until exiting the field. For example, I would
like to set a field to a length of 20 characters and prevent the user from
entering any data past the 20th character. I can create borders for the
fields but Excel allows the users to keep typing, and they keep typing.


I have a field in a form that is linked to a very simple table. The three options are "Negative, Positive, Stable". When these items appear in the List Box on the form they are aligned to the left. How can I centre them? The option is greyed out on the toolbar when I select the List Box.

I want to create a file with fixed length records (lines). The fields
(columns) have defined lengths. When I export the file, it should be in a
flat text format, there should be no spacing between columns of data, and the
total line length should be exactly what I want.

How do I do this? What format should I export the data into?


I'm having real difficulty in creating a chart in a form and was hoping someone might have some code/ideas that could help.

I have previously found some code that I know works on XP but doesn't run on w2000.

Sub loadgraphdata()

'Create arrays for the x-values and the y-values
Dim xValues() As Variant
Dim yValues() As Variant

'load the xtitles from the spreadsheet

thecol = 2
arraycount = 0
While Cells(1, thecol) ""
ReDim Preserve xValues(arraycount)
xValues(arraycount) = Cells(1, thecol)
arraycount = arraycount + 1
thecol = thecol + 1

'load the yvalues into the spreadsheet
thecol = 1
arraycount = 0
While Cells(2, thecol) ""
ReDim Preserve yValues(arraycount)
yValues(arraycount) = Cells(2, thecol)
arraycount = arraycount + 1
thecol = thecol + 1

' xValues = Array("A", "B", "C", "D", "E", "F")
' yvalues = Array(104737, 50952, 78128, 117797, 52902, 80160, 47491, _

'Create a new chart
Dim oChart As WCChart
Set oChart = UserForm1.ChartSpace1.Charts.Add

'Add a title to the chart
oChart.HasTitle = True
oChart.Title.Caption = Range("b4").Value

'Add a series to the chart with the x-values and y-values
'from the arrays and set the series type to a column chart
Dim oSeries As WCSeries
Set oSeries = oChart.SeriesCollection.Add
With oSeries
.Caption = Range("a2").Value
.SetData chDimCategories, chDataLiteral, xValues
.SetData chDimValues, chDataLiteral, yValues
.Type = chChartTypeColumnClustered
End With

'Format the Value Axes
oChart.Axes(chAxisPositionLeft).NumberFormat = "$#,##0"
oChart.Axes(chAxisPositionLeft).MajorUnit = 20000

'Show the legend at the bottom of the chart
oChart.HasLegend = True
oChart.Legend.Position = chLegendPositionBottom

End Sub

With some tinkering I can get the chart to appear but can't load the data onto it.

All help appreciated.


We have a need where in we need to link fields in a form, created in a word
document, to an excel sheet.

What we are trying to see is the possibility that whenever the data in a
particular form is changed, the same is updated in excel.

Is it possible? Can anyone please help and advice?

Warm regards



I am beating my head against the wall. I am attempting to create a
calculated field in a PivotTable with a formula like so:

=IF(myDate > 0, 1, 2)

myDate being a field name from the field name list for the Pivot
Table. It is available from the field list in the Insert Calculated
Field dialog box. When I select it, it is added to the formula with
single quotes. I have followed the syntax I have found on the web as
well as in the QUE book "Using Microsoft off Excel 2007". Nothing
seems to work. It seems so simple.

Ultimately I need a formula that looks for blank fields and with a
little logic, determine a step in a process and the number of days
stuck at the process. The data is coming from a SharePoint list.

Please let me know if I am going about this the wrong way. I have been
at it for three days and all over the web trying to figure it out.

The bottom line is that I want to say:

If this field value is blank, then give me the number of days between
datefield1 and datefield2.

Please assist. Any and all help is so appreciated!

Using Excel 2007 RTM on Vista RTM


I am trying to compare 4 fields in a row with rows in another worksheet and then insert a value based on the comparison, example below

Worksheet "data entry"

Company Name|Postcode|Match Type

Steve Shine | SG4 7PT |

Worksheet "compare"

Co Name 1 |Co Name 2 |Co Name 3 |Postcode|Current

Steve Shine| NULL| Steve| SG4 7PT| Y
NULL| Paul Parts| NULL| SG4 7PT| Y
Simon auto|Simon auto|Simon auto|mk45 8rm| N

I need to compare the row in "data entry" with all the rows in "compare" and

IF Company Name = Co Name 1 in worksheet "compare"
OR Company Name = Co Name 2 in worksheet "compare"
OR Company Name = Co Name 3 in worksheet "compare"
AND Postcode in "data entry" = Postcode in "compare"
CASE "compare" Current = Y "data entry" Match Type = Current
CASE "compare" Current = N "data entry" Match Type = Old
ELSE "data entry" Match Type = NULL

Then move down 1 row in "data entry" and run the comparison again. In the example above Current would be inserted in the field Match Type.

I have been struggling with this for a while with no success if anybody can help it would be much appreciated.



I need to populate the fields of a form with data from a range when the user selects a particular entry from a combo box. Using the selection from the combo box, this routine will find that value in column A of another worksheet in the file and populate all the other fields on the form with data in that row.

Any ideas?

I have a form with an In and Out Time. I want the user to input the time with no colon and no AM or PM. Then a Calculate button will give the total hours. Is this possible and the am pm is not necessary but would be helpful. Attached is a sample form that does work when the time is entered in as 12:00:00 AM format. Thank you for any help.

Hi, does anyone know how to clear all the text boxes in a form using vba??
Once i have pasted collected information to a spreadsheet, I want to clear around 100 text boxes before collecting the next round of information.



I have a problem. I'm supposed to sum values in every second field in a row(and the length of columns that have to be summed varies).

So basicly I have to SUM(J4,L4,N4,P4.....) and SUM(K4,M4,O4.....) for a variable number of columns.

Any help?

How can I declare an Array in a form that will function as "Public". Those
arrays values can be updated for every modules that is called when this form

I have an example:

This array will be like:

ReDim ErrorValue(1 To Len(myVal)) As String

*I need it PUBLIC because those array values are going to be updated in each
of the following functions:

function check4code1()
frmCheckCells.ErrorValue(k) ="Type 1" 'for check4code1
end function

function check4code2()
frmCheckCells.ErrorValue(k) ="Type 2" 'for check4code2
end function

….and More functions….

if no error is found then ErrorValue will be “”

Then, at the end of the process. The form will have a result like this

Sub searchX()
lbltxtErrorFound=ErrorValue(0) + ErrorValue(1) +ErrorValue(2)……

End sub

Can it be the best option ?



I want to populate TextBox2 in a form with the value in TextBox1 that uses a VLOOKUP function. I have scoured the net and have come up with this.

Private Sub TextBox1_Change()

   Dim rngFind As Range
   Set rngFind = Sheet1.[A7:J86]
   On Error Resume Next
   TextBox2.Text = WorksheetFunction.VLookup(TextBox1.Value, rngFind, 10, False)
   On Error GoTo 0

End Sub

However, this does not appear to do anything. I type a string that matches a value in the lookup range, and nothing appears to happen. I have just recently acquainted myself with VB and am very green. Please tell me it is something simple I am missing.

Thanks. B

Can you create an online type form with excel 2003? I tried to use the form
button but it looked like it would only handle one field at a time. Do I need
to use a template type thing? And how do I accomplish all of this? Thanks in

In this example I will show how we can populate a combobox in a userform with data from a database.

What we need:
* MS Windows 2000 or above
* MS Excel 2000 or above
* MDAC 2.5 or above (ADO Library)

Step 1
Add a reference to MS ADO Library x.x via Tools | Reference... in the VB-Editor.

Step 2
Insert following code in a standardmodule:

Sub Populate_Combobox_Recordset() 
    Dim cnt As ADODB.Connection 
    Dim rst As ADODB.Recordset 
    Dim stDB As String, stConn As String, stSQL As String 
    Dim xlCalc As XlCalculation 
    Dim vaData As Variant 
    Dim k As Long 
     'In order to increase the performance.
    With Application 
        xlCalc = .Calculation 
        .Calculation = xlCalculationManual 
        .EnableEvents = False 
        .ScreenUpdating = False 
    End With 
     'Instantiate the Connectionobject.
    Set cnt = New ADODB.Connection 
     'Path to and the name of the database.
    stDB = ThisWorkbook.Path & "" & "Test.mdb" 
     'Create the connectionstring.
    stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & stDB & ";" 
     'Create the SQL-statement.
    stSQL = "SELECT * FROM tblData" 
    With cnt 
        .CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
        .Open stConn 'Open connection.
         'Instantiate the Recordsetobject and execute the SQL-state.
        Set rst = .Execute(stSQL) 
    End With 
    With rst 
        Set .ActiveConnection = Nothing 'Disconnect the recordset.
        k = .Fields.Count 
         'Populate the array with the whole recordset.
        vaData = .GetRows 
    End With 
     'Close the connection.
     'Manipulate the Combobox's properties and show the form.
    With frmData 
        With .ComboBox1 
            .BoundColumn = k 
            .List = Application.Transpose(vaData) 
            .ListIndex = -1 
        End With 
        .Show vbModeless 
    End With 
     'Restore the settings.
    With Application 
        .Calculation = xlCalc 
        .EnableEvents = True 
        .ScreenUpdating = True 
    End With 
     'Release objects from memory.
    Set rst = Nothing 
    Set cnt = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The most notable thing is that we need to transpose the horizontal array vaData to an vertical to add it to the combobox.

Happy dataretrieving

i'm trying to populate a cell in an excel spreadsheet with a form field in a
word document. is this possible?

I'm trying to update a a date field in a table using the following code:

MySQL = "UPDATE [tblGStaff] Set [Next Eval THRU Date] = " & CDate(Format((Me.Next_Eval_THRU_Date), "Short Date")) & " Where [ID] = " & CInt(Me.ID)
CurrentProject.Connection.Execute MySQL

When I run this the date that should get posted to the table is 5/15/2007, what ends up going there is 12/30/1899. In fact this date is put in for all of the entries in the table that have been updated by the above SQL statement. In tblGstaff I have the Next Eval THRU Date format as a short date. If I remove this format it updates the table with a time.

In the form the Next Eval Thru Date is an input by the user.

My last attemp was to try the CDate as shown above but still no luck.

Any suggestions on how to have the right date posted to the table would be greatly appreciated.


Im in the process of putting together a home finance system for my university project and one of the key requirements of the system is that the system should avoid the need for the user to interact directly with the spreadsheet. So currently everything it does is through forms.....e.g. Add accounts, add transactions to accounts etc.

Now I need to somehow implement the loan amortization feature and I was wondering if its possible to somehow 'addin' a spreadsheet loan amortization schedule the system creates on to a form which the user can interact with? (instead of directly with the spreadsheet)

If this is not possible anyone got any others ideas on how it might be possible to display the loan schedule?

Many Many Thanks

Can I create a worksheet with fixed numbers of row and column?

e.g. the worksheet only has 40 columns and 20 rows

Is it possible to re-arrange the sequence of fields (edit boxes) used in a form. Now, it doesn't follow a logical sequence when using the "tab" key to browse through the fields.
Any suggestions would be appreciated

I've created a form in Excel which has a number of pages (via multipage property)

There is a commandbutton that moves data from one page to another

Is there a way in which the user is taken to the new page without the need to click on the page tab?

Thanks in advance


I am looking to create a listbox in a form that can only select the patients that have not been admitted.

I can select the rows that have the blank cells in the current region, not sure how to tie this in with my list box in the form though. I can't see any property in the list box to link to a range, maybe I have to use a combo box instead?


Is there a way to automatically copy text in a cell (say A4 = "File Name: Test 23 (July 16, 2007).txt") into a text field in a graph?

I have a spreadsheet with multiple graphs.

I dump numbers into the spreadsheet and it automatically adjusts the graphs.

I can't figure out a way to create titles in the graphs that are unique to the data.

Right now I have to copy and paste the text from the spreadsheet into each graph - 25 graphs in total.

Any suggestions?


I am creating a user interface in excel 2003, windows XP. I want to have the multipage control functionality in my worksheet but without having it exist in a bulky and awkward form.

For instance, I have a listbox embedded in my excel worksheet without being encapsulated in a form. However, the control toolbox only gives me a multipage option for userforms. i.e. I can't embed a multipage control directly into my worksheet.

I couldn't find out if excel 2007 allows this. If it doesn't, are there customized patches of some sort that I could download to get this functionality?


No luck finding an answer? You could always try Google.