Formating a form in Excel

[Kamlesh Patel]

Dear Mr Excel,

We are looking for the following formats to be done in the excel form, Kindly advise on the formulas that can be used

Formats to be done are as follows:

1) TO restrict the format layout, so as no additional cells can be inserted of deleted.

2) TO automatically convert all data entered upper case, no matter in whichever case it is being updated.

3) TO restrict the number of characters that can be entered (including blank spaces) in such way

a) That any additional character entered over and above the fixed limit should automatically be updated in the next cell (I.e. the cell below it)

OR

b) It should not allow the user to update additional data once the prescribed limit is attained (but without the user actually coming out of the cell and trying to save the data)

 

[Russ At Index]

Hi Kamlesh Patel,Welcome To The Board,

I can assist with points 2 & 3 , sorry cannot find
much to help with point 1

Point 2.
What ever you type in column A will automatically be
transposed as UPPER CASE..... Right click your sheet tab
& select view code.
Under the open explicit line paste this VB info :

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A100")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub

Save as you like !!!!
*** Change the range ("A1:A100)) to whatever best suits your needs ***

Point 3.
To restrict the number of characters in a cell , follow the instructions
below.
40 is used as the example , but you can adjust to suit.

Data-->Validation--Allow Text Length-->Less than or equal to-->Maximum-->40.

Well thats it i am afraid , i'm off to watch England play Rugby
(Possible battering ) then to watch England try & seek revenge
against Argentina !!!! ( Definate draw )

Hope the above has helped you

Russ.

 

[Kamlesh Patel]

Tks Russ,

How was your day and how did Englad do against Argentina !!

Wanted a bit more help from you on the above ponits,

As regards to point 2 it works only when the security level of the PC is brought low or medium.If I want to circulate a form having the above command written, the same would not work because the security level of that PC might be high.Is there a way as to the VB programm will work no matter whatever the security level of that PC is?

As regards to point 3 can we restrict thje number of characters that can be entered in a particular field , however can we restrict in such a way that if the user ahappens to exceed the defined limit than the extra, additional characters entered automatically get updated in the cell below the exsisting cell.

Your sugessions are of great help to me.Keep helping.


"A smile is worth million but it doesn't cost a cent...."

 

[Kamlesh Patel]

Tks Russ,

How was your day and how did Englad do against Argentina !!

Wanted a bit more help from you on the above ponits,

As regards to point 2 it works only when the security level of the PC is brought low or medium.If I want to circulate a form having the above command written, the same would not work because the security level of that PC might be high.Is there a way as to the VB programm will work no matter whatever the security level of that PC is?

As regards to point 3 can we restrict thje number of characters that can be entered in a particular field , however can we restrict in such a way that if the user ahappens to exceed the defined limit than the extra, additional characters entered automatically get updated in the cell below the exsisting cell.

Your sugessions are of great help to me.Keep helping.


"A smile is worth million but it doesn't cost a cent...."