User keys in serial number range, labels auto-fill

[buscher75]

I would like create a user friendly word document that promts the user to
enter in the serial number range ex: RED0001 - RED1001 and have the labels
automatically fill in as many sheets as needed. I am converting these
numbers into a barcode.
I do not know a lot about this so details would be wonderful. Any ideas on
how to do this would be greatly appreciated. Thank You.

 

[Doug Robbins - Word MVP]

Start with a document containing one page of empty labels (labels in Word
are just table cells with fixed dimensions) and then run a macro containing
the following code:

Dim i As Long, j As Long, n As Long, m As Long
Dim labeltext As String
Dim startnum As Long
Dim endnum As Long
Dim labels As Long
Dim newrow As Row
labeltext = InputBox("Enter the text for the label")
startnum = InputBox("Enter the starting number")
endnum = InputBox("Enter the last number")
labels = InputBox("Enter the number of labels in a row")
n = 0
With ActiveDocument.Tables(1)
For m = .Rows.Count To 2 Step -1
.Rows(m).Delete
Next m
For i = 1 To (endnum - startnum + 1) / labels
Set newrow = .Rows.Add
With newrow
For j = 1 To labels
.Cells(j).Range.Text = labeltext & Format(startnum + n,
"0000")
n = n + 1
Next j
End With
Next i
.Rows(1).Delete
End With

It will first ask for the text part of the label (RED), then the first
number to use 1 and the last number to use 1001 and the number of labels on
each row.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[buscher75]

Thanks for the help Doug, it works! What I did not think about at the time
is the fact these serial numbers would be converted to barcode and therefor I
would need to automatically add an * to the front and back of the serial
number. Is there way I can add this to the code or do I need to prompt the
user to do this. Also, when you add * to the front and back of a word, the
word program automatically changes it to BOLD. I would need to shut this off
also. I hope you can help with this. I appreciate your time.

 

[Doug Robbins - Word MVP]

A simple change to the

.Cells(j).Range.Text = "*" & labeltext & Format(startnum + n, "0000") & "*"

command will do that. Nothing turns to bold here so I am not sure what is
going on there.


Dim i As Long, j As Long, n As Long, m As Long
Dim labeltext As String
Dim startnum As Long
Dim endnum As Long
Dim labels As Long
Dim newrow As Row
labeltext = InputBox("Enter the text for the label")
startnum = InputBox("Enter the starting number")
endnum = InputBox("Enter the last number")
labels = InputBox("Enter the number of labels in a row")
n = 0
With ActiveDocument.Tables(1)
For m = .Rows.Count To 2 Step -1
.Rows(m).Delete
Next m
For i = 1 To (endnum - startnum + 1) / labels
Set newrow = .Rows.Add
With newrow
For j = 1 To labels
.Cells(j).Range.Text = "*" & labeltext & Format(startnum +
n, "0000") & "*"
n = n + 1
Next j
End With
Next i
.Rows(1).Delete
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Jay Freedman]

I suspect it's the AutoFormat As You Type setting of "*Bold* and _italic_
with real formatting". Uncheck that option and test again.

If that's the right item, add code to the macro to save the current value of
Options.AutoFormatAsYouTypeReplacePlainTextEmphasis and set that option to
False at the beginning of the code; restore the original value at the end of
the code.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.