Advancing A Number With The Push A Button
 

Greeting,

I tried this in the misc group, but I think that was the wrong group,
so here goes, again....

I have an invoice that uses a button to advance the invoice number by
1. When I wrote it the numbers were just numbers, but since then a
letter has been added to the front of the number (was 17445 now
D-017445). My advancing code no longer works and I don't know why!

Here is the code:

Private Sub GetNextInv_Click()
Range("InvNoLast").Value = Range("InvNoLast").Value + 1
End Sub

Anyone have an idea on how to fix it?

Any help would be most appreciated.

TIA

-Minitman

Advancing A Number With The Push A Button
 

Try something like

Private Sub GetNextInv_Click()
Dim S As String
Dim N As Long
Dim Pos As Integer
S = Range("InvNoLast").Value
Pos = InStr(1, S, "-")
N = CLng(Mid(S, Pos + 1))
N = N + 1
Range("InvNoLast").Value = Left(S, Pos) & Format(N, "000000")
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Minitman" wrote in message
...
Greeting,

I tried this in the misc group, but I think that was the wrong
group,
so here goes, again....

I have an invoice that uses a button to advance the invoice
number by
1. When I wrote it the numbers were just numbers, but since
then a
letter has been added to the front of the number (was 17445 now
D-017445). My advancing code no longer works and I don't know
why!

Here is the code:

Private Sub GetNextInv_Click()
Range("InvNoLast").Value = Range("InvNoLast").Value + 1
End Sub

Anyone have an idea on how to fix it?

Any help would be most appreciated.

TIA

-Minitman

Advancing A Number With The Push A Button
 

You got a couple of replies there.

Minitman wrote:

Greeting,

I tried this in the misc group, but I think that was the wrong group,
so here goes, again....

I have an invoice that uses a button to advance the invoice number by
1. When I wrote it the numbers were just numbers, but since then a
letter has been added to the front of the number (was 17445 now
D-017445). My advancing code no longer works and I don't know why!

Here is the code:

Private Sub GetNextInv_Click()
Range("InvNoLast").Value = Range("InvNoLast").Value + 1
End Sub

Anyone have an idea on how to fix it?

Any help would be most appreciated.

TIA

-Minitman

--

Dave Peterson

Advancing A Number With The Push A Button
 

Private Sub GetNextInv_Click()
Range("InvNoLast").Value = Left(Range("InvNoLast").Value, 2) & _
Format(Right(Range("InvNoLast").Value, _
Len(Range("InvNoLast").Value) - 2) + 1, "000000")
End Sub


--
Regards,
Tom Ogilvy

"Minitman" wrote in message
...
Greeting,

I tried this in the misc group, but I think that was the wrong group,
so here goes, again....

I have an invoice that uses a button to advance the invoice number by
1. When I wrote it the numbers were just numbers, but since then a
letter has been added to the front of the number (was 17445 now
D-017445). My advancing code no longer works and I don't know why!

Here is the code:

Private Sub GetNextInv_Click()
Range("InvNoLast").Value = Range("InvNoLast").Value + 1
End Sub

Anyone have an idea on how to fix it?

Any help would be most appreciated.

TIA

-Minitman

Advancing A Number With The Push A Button
 

Hey Dave,

Thanks for the heads up on the other group, I messed up in that one.

-Minitman



On Sat, 31 Jul 2004 16:57:34 -0500, Dave Peterson
wrote:

You got a couple of replies there.

Minitman wrote:

Greeting,

I tried this in the misc group, but I think that was the wrong group,
so here goes, again....

I have an invoice that uses a button to advance the invoice number by
1. When I wrote it the numbers were just numbers, but since then a
letter has been added to the front of the number (was 17445 now
D-017445). My advancing code no longer works and I don't know why!

Here is the code:

Private Sub GetNextInv_Click()
Range("InvNoLast").Value = Range("InvNoLast").Value + 1
End Sub

Anyone have an idea on how to fix it?

Any help would be most appreciated.

TIA

-Minitman

Advancing A Number With The Push A Button
 

Hey Chip,

That works great. I was wondering, how would I do this with a
ScrollBar instead of separate buttons? Is it even possible? I need
to go both forward and backward.

Thanks for the help.

-Minitman


On Sat, 31 Jul 2004 16:52:17 -0500, "Chip Pearson"
wrote:

Try something like

Private Sub GetNextInv_Click()
Dim S As String
Dim N As Long
Dim Pos As Integer
S = Range("InvNoLast").Value
Pos = InStr(1, S, "-")
N = CLng(Mid(S, Pos + 1))
N = N + 1
Range("InvNoLast").Value = Left(S, Pos) & Format(N, "000000")
End Sub

Advancing A Number With The Push A Button
 

Thanks Tom

This looks good, unfortunately, I already went with Chip's solution,
but thanks for the reply.

-Minitman

On Sat, 31 Jul 2004 18:13:45 -0400, "Tom Ogilvy"
wrote:

Private Sub GetNextInv_Click()
Range("InvNoLast").Value = Left(Range("InvNoLast").Value, 2) & _
Format(Right(Range("InvNoLast").Value, _
Len(Range("InvNoLast").Value) - 2) + 1, "000000")
End Sub