Custom Format

[adamsm]

Hi,

How could I format the cell to make it appear as A4AM0000?

Suppose if the user writes A4AM0001 the format will change from A4AM0000 to A4AM0001.

Any help on this would be kindly appreciated.

Thanks in advance.

 

[Boller]

If you mean you want to input a number and have the cell display A4AM plus a four digit number based on the input number then format the cell :-

"A4AM"000#

 

[adamsm]

Thanks for the help. But how could I make a code that would change the numbers on serial basis?

 

[Boller]

Thanks for the help. But how could I make a code that would change the numbers on serial basis?

Do you mean you want to use VBA to incease the four digit number at the end by 1 ?

If so, when do you want the number changed and by what means?

Are you looking for a sequential numbering method for a Template?

Please provide more info on what you are trying to do.

 

[adamsm]

Yes, I meant I want to increase the four digit number at the end by 1. And I want the number to be changed when the user runs the macro.

Suppose if the user writes A4AM0001 the format will change from A4AM0000 to A4AM0001.

And yes I'm looking for a sequential numbering method for a Template.

I hope I've made my question clear.

Thanks in advance.

 

[Boller]

"Suppose if the user writes A4AM0001 the format will change from A4AM0000 to A4AM0001."

I don't know what this means. Do you want the number to be changed by a macro, or do you want the user to change it manually?

I will assume that :

- You are using XL2003 or earlier
- You have a template (.xlt) from which you create workbooks (.xls)
- the workbooks will be numbered sequentially
- the number of each workbook will be displayed in cell A1
- you want the number of each workbook to be entered by a macro
- the user cannot alter or change a number once it has been entered

Based on these assumptions, format A1 as explained in my earlier post, and then :-

1. To enter the next sequential number in A1

Sub Insert_Next_SeqNbr()
Dim Nbr As Long
Nbr = GetSetting("XLInvoices", "Invoices", "NextNbr", 1)'If you want to start at some number other than 1, amend accordingly
If Right(ThisWorkbook.Name, 4) = ".xls" Then
    With Sheets("Sheet1") 'Change sheet name as required
        If .[A1] = "" Then 'Amend cell ref as required
            Application.EnableEvents = False
            .[A1] = Nbr 'Amend cell ref as required
            Application.EnableEvents = True
            SaveSetting "XLInvoices", "Invoices", "NextNbr", Nbr + 1
        End If
    End With
End If
End Sub

This macro could be run from a worksheet button, or called from an event procedure (e.g. Workbook_BeforeSave).


2. To prevent a user from manually entering or changing the number in A1, put this in the sheet module :-

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [A1]) Is Nothing Then 'Amend cell ref as required
    With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
    End With
End If
End Sub

3. To reset the sequential numbering to 0000

Sub Delete_SeqNbr_Registry_Entry()
On Error Resume Next
DeleteSetting "XLInvoices", "Invoices"
End Sub

 

[Boller]

I see that you already asked this question previously :-

http://www.mrexcel.com/forum/showthread.php?t=548822

 

[adamsm]

Thanks for the help. It worked fine and I do really appreciate it.