New invoice using macro

[KAYE04]

Hi,

I have created a form in excel with invoice number which is alpha numeric.
im having a hard time fixing the error about the leading zeros in my invoice number. My invoice number is FIN19-0001.
i want to change it from FIN19-0001 to FIN19-0002, FIN19-0003 and so on.
but every time i click the button intended for that macro it will become FIN19-2. the 3 zeros are not included.
how can i fix it. can somebody help me.
thank you so much in advance.
\here is my code
Sub NextInvoice()
Range("B7").Value = Left(Range("B7").Value, 6) & 1 + Mid(Range("B7").Value, 7, 4)

 

[dmt32]

i cant understand what are you referring to. im sorry

In cell B7 enter a number say for example the number 2 but just enter a number. Then run the code I provided as published.

Dave

 

[KAYE04]

ow thankyou finally...

thankyou so much it works

 

[dmt32]

ow thankyou finally...

thankyou so much it works

Most welcome - glad resolved

Dave

 

[KAYE04]

sir, i still have some questions
what if i want to locked B7 so that no one can edit it. but the next invoice button will still run.
is that possible?
thank you

 

[dmt32]

sir, i still have some questions
what if i want to locked B7 so that no one can edit it. but the next invoice button will still run.
is that possible?
thank you

You would have to protect the sheet for cells locked property to have any effect

Try adding lines of code shown to your procedure

Sub NextInvoice()


    ActiveSheet.Unprotect Password:="YOUR_PASSWORD_HERE"


    With Range("B7")
        .Value = .Value + 1
        .NumberFormat = """FIN19-""0000"
    End With
    Range("D11:I11,D12,B26:I32").ClearContents


    With ActiveSheet
        .EnableSelection = xlUnlockedCells
        .Protect Password:="YOUR_PASSWORD_HERE"
    End With


End Sub

enter your password (case sensitive) where shown in red.

Dave

 

[KAYE04]

Hi sir,

i just want that particular cell the B7 be locked and some other cell, not all sheet, is that possibe?

 

[dmt32]

Hi sir,

i just want that particular cell the B7 be locked and some other cell, not all sheet, is that possibe?

You can do something like following

Sub NextInvoice()
    With ActiveSheet
    
        .Unprotect Password:="YOUR_PASSORD_HERE"


    With .Range("B7")
        .Locked = True
        .Value = .Value + 1
        .NumberFormat = """FIN19-""0000"
    End With
    
    With .Range("D11:I11,D12,B26:I32")
        .Locked = False
        .ClearContents
    End With
    
        .EnableSelection = xlUnlockedCells
        .Protect Password:="YOUR_PASSORD_HERE"
    End With
End Sub

Ranges in RED are unlocked - you can add other ranges if required

Dave

 

[KAYE04]

an error occurred it says unable to set the locked property of the range class

 

[dmt32]

an error occurred it says unable to set the locked property of the range class

error suggests sheet is still protected.

Sub NextInvoice()
    With ActiveSheet
    
        .Unprotect Password:="YOUR_PASSORD_HERE"


    With .Range("B7")
        .Locked = True
        .Value = .Value + 1
        .NumberFormat = """FIN19-""0000"
    End With
    
    With .Range("D11:I11,D12,B26:I32")
        .Locked = False
        .ClearContents
    End With
    
        .EnableSelection = xlUnlockedCells


        .Protect Password:="YOUR_PASSORD_HERE"
    End With
End Sub

ensure the line in bold is unprotecting the sheet

Dave

 

[KAYE04]

thankyou so much.) i got it now. thanks for your help