Como puedo generar números de registro automáticos consecutivos en VBA

Yo lo haría con una función a la que le paso el ID anterior por argumento y basado en él, generaría el próximo ID lógico. La ventaja de esto es que luego la puedes llamar desde cualquier parte sin tener que repetir códigos y si todo tu libro tiene consistencia en formatos puedes usar la misma función para generar otros IDs en otras hojas o bases.

Como bien dice James Bond el formato debe ser cambiado para que Excel no piense que es una fecha, sin embargo, no veo necesario hacer eso por código ya que parece una tarea redundante. Mi consejo es seleccionar toda la columna y cambiar el formato una única vez (tarea de Excel básico) Formato -> Personalizado -> 0000-####.

La función sería esta:

Public Function NextID(PreID As Long) As Long
Dim CrrtYr As Integer: CrrtYr = Year(Now)
Dim INCrement As String: INCrement = Left(CStr(PreID), Len(CStr(PreID)) - 4)
NextID = CLng(INCrement) + 1 & CrrtYr
End Function

Debido a que la operación es simple, la función no es a prueba de errores por naturaleza, cualquier validación debería ser independiente, como muestro en el siguiente ejemplo:

Video demo

NOTA: La segunda macro que se muestra en el vídeo es pura demostración, ya tu ejecutarías la función como desees.

Hola Andy, he probado tu método y me resulta algo complicado adaptarlo. Te cuento un poco más Tengo unos datos que se introducen en un form y van a una hoja.

En la columna A esta el ID del pedido empezando por 0001-2019 (aun no hay registros).

Los datos están en Dades_taula (empieza en la celda A3) en la hoja llamada "Dades"

Me gustaría que empezara la numeración por 0001-(año) y que en el form cuando vaya introducir otro pedido automáticamente detectara el ultimo ID introducido y que le sumara 1 Ej: 0002-2019.

¿Cómo lo podría hacer?

Te entendi perfectamente desde el principio, y la funcion hace exactamente eso. Ya has valorado las respuesta, supongo que pudiste resolverlo.

Salud2

La verdad que no he logrado adaptarlo. Estoy aprendiendo en esto de VBA. Tengo la función copiada ya pero no se muy bien como ejecutarla.
He puesto esto en el evento Initialize para que pueda cargar en Label el numero consecutivo pero no me hace nada.
¿Cómo podría ejecutar la función para que haga sun trabajo?

Es una macro como otra cualquiera, imagina que en lugar de decir Public Funcion dice Sub. La diferencia es que con public function puedes devolver un resultado directamente.

Lo otro es que recibe una variable por argumento.

Es como si dijeras:

Call NextID

Pero debes pasarle el ID anterior por argumento, quedaria

Call NextID(IDanterior)

Ejemplo:

Call NextID(12019)

Y la función devolvería un 22019

Ahora bien, como dije antes, no es necesario usar Call ya que la función devuelve el valor directamente, por lo que simplemente dirías:

Label1.Caption = NextID(ID_anterior)

Donde ID_anterior es una variable que contiene el ultimo ID usado.

En programación a esto se le llama Pase de variables, o pase de argumentos o parámetros.

Es pasar una variable de una función a otra "por argumentos" para hacer operaciones con ella fuera y devolver un valor nuevo a la función principal.

Argumentos o Parámetros son las variables que le pasas a una macro entre paréntesis.

Sub Macro(argumento1, argumento2, etc etc)

End Sub

Me va quedando claro como usar las funciones y para que sirven. Tengo esta función pegada en el código

Public Function NextID(PreID As Long) As Long
Dim CrrtYr As Integer: CrrtYr = Year(Now)
Dim INCrement As String: INCrement = Left(CStr(PreID), Len(CStr(PreID)) - 4)
NextID = CLng(INCrement) + 1 & CrrtYr
End Function

Entonces entiendo que si quiero ver cual seria ID al iniciar el formulario debo poner este codigo  en el evento Initialize no?

num_consecutiu.Caption = NextID(ID_anterior) 
'asi se llama el label del formulario

En mi cabeza parece logico este razonamiento pero a la practica me da error. Me marc (ID_anterior) y me dice no se ha definido la variable.
No logo entender el porque no funciona y de que otra manera puedo hacer.
¿Cuál es mi error?

Ese es un error de compilacion, sucede si estas usando Option Explicit al comienzo del modulo, y no has "declarado" la variable con:

Dim ID_anterior As Long

Es una buena practica declarar variables y muy mala no hacerlo.

A parte de declararla, tambien debes darle un valor.

Dim ID_anterior As Long

ID_anterior = 'aqui asignas el valor, debes obtener el ultimo id de alguna manera

Entonces ya luego estara lista para ser usada

num_consecutiu.Caption = NextID(ID_anterior)

Hola Anda me has puesto 2 veces esta variable, ¿es correcto?

Dim ID_anterior As Long

He intentado realizar esto que me comentas y no me funciona.
He declarado las variables y he puesto:

Dim ID_anterior As Long
ID_anterior =Range("A65536").End(xlUp).Row

y despues he intentado copiar el dato en el label

num_consecutiu.Caption = NextID(ID_anterior)

Pero me sigue dando un error "Llamada o argumento de procedimiento no válido (error 5)"
Hasta he quitado el Option Explicit del principio y copiado el código sin esta ultima parte y me sigue dando este error

Range("A65536").End(xlUp). Row

Eso lo que devuelve es el numero de la ultima fila usada, no el contenido de la celda.

Esto se esta haciendo muy largo y tu ya has dado la calificación desde el comienzo. Te sugiero tomar la otra alternativa que te dieron