×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

how to use vba to mk stickers..

how to use vba to mk stickers..

how to use vba to mk stickers..

(OP)
example: (refer help10.xls)
http://www.geocities.com/remember_it/help10.xls  ;

Option Explicit
In my job, I often have to printer stickers which show the info of products. However, I find the traditional way VERY INCONVENIENT and UNEFFICIENT.

Traditional Flow (of making stickers):
1) extract product info (the format of which is unsuitable for my company) from the details provided by the supplier
2) enter the extracted product info to my company's checklist sheet
    eg. Sheets("checklist")
3) re-enter the product info into my company's sticker sheet
    eg. Sheets("sticker")
--------------------------------------------------------------------------------------------------------------------------------
Sub Problem()
Since
    Sheets("sticker") 's format <> Sheets("checklist") 's format
Therefore
    "paste all" CANNOT be used
    using "copy and paste" is EXTREMELY INCONVENIENT
    'actually, re-key-in the data is EVEN FASTER then "copy and paste" !
On Error
    the info in Sheets("sticker") REQUIRES DOUBLE CHECK
    'which is unefficient
End Sub

--------------------------------------------------------------------------------------------------------------------------------
Sub Difficulties()
Since
   the LENGTH of product info for EACH sticker in Sheets("sticker") is DIFFERENT
Therefore
   the RANGE of EACH STICKER is DIFFERENT
   the sizing and positioning for EACH sticker VARIES
   'especially the "content", do not know how to manage its number of row
End Sub
--------------------------------------------------------------------------------------------------------------------------------
Sub DraftSoln()
If the product info in Sheets("checklist") can transfer to Sheets("sticker") _
   AUTOMATICALLY and INTELLIGENTLY by vba Then
  It will be GREAT
End If
End Sub
--------------------------------------------------------------------------------------------------------------------------------
Sub SolnDirection()
Select Case Direction
  Case by FORMULA
        Sheets("sticker").Cells().value can be QUERIED from Sheets("checklist").Cells
        Difficulties
  Case vba1
      using vba to fill Sheets("sticker").Cells().value with CORRESPONDING RECORDS in Sheets("checklist").Cells
      Difficulties
  Case vba2
     using vba to MAKE a new sticker sheet (still using the SAME format as Sheets("sticker"))
     'MAKE (CREATE from zero)
     'MAKE...like the path how REPORT is made in MSAccess (CREATE from data table)
     Difficulties
End Select
End Sub
--------------------------------------------------------------------------------------------------------------------------------
Sub Questions()
    any good suggestions?
            If Case vba2 Then
               how to write the vba codes? (it seems complicated..) ***the size of Print_Area in Sheets("sticker") CANNOT be changed!!!
            End If
End Sub

RE: how to use vba to mk stickers..

Page requested not found.
  Reading your note makes me think you understand the logic needed to do what you want to do.

First are all 3 sheets in the same workbook?
  
It sounds like you shoud be able to put in your "sticer sheet" =checklist!a1  if the information you want is always in a1 on the checklist sheet
if its always in a1 or a2 or a3 and the other two are blank you can say = checklist!a1 & checklist!a2 & checklist!a3

Please try to explain your problem more clearly and in english instead of sub() language.  I'll see if I can help more.

Tim

RE: how to use vba to mk stickers..

Will look at spreadsheet over the weekend - note to FacilitiesCAD above - have to clear few blanks from end of link to get to it.....
Mutt.

RE: how to use vba to mk stickers..

Alright,  I opened the file and the code you want doesn't look to hard. First I want to thank you because your file showed me vlookup wich I had never seen before.

You can aproach this from a few different ways. Either you can set up a macro that you run after each data entry or one that does the whole sheet every time.
  From the looks of it you can have 1 to 5 contents (is this right?)

Sub Problem() - write macro to do the right pasting for you (from data_entry)

Sub Difficulties()- use a loop(for i=1 to 5) and check if the cell is empty ="" this way you can have 1 to 5 contents


DraftSoln() it will be much easyer to take the data from data_entry than from your checklist.  The way it is now

here are some things to think about when making the stickers
what is the print area, where do you want to put the information after you fill your first page?

in the simplest case your vba code might look something like this

sum make_sticker()
Dim x as integer  'column
Dim y as integer  'row
Dim x1 as integer  'user input column
Dim y1 as integer  'user input row

dim i as integer  'used in loops


x=1 'initial row
y=4 'initial column


cells(x,y) = "My Company"
cells(x+1,y)=":"
cells(x+2,y)="real company name"
y=y+1
cells(x,y) = "Date"
cells(x+1,y)=":"
cells(x+2,y)=TEXT(TODAY(),"dd/mm/yyyy")
y=y+1
cells(x,y) = "Suplier"
cells(x+1,y)=":"
cells(x+2,y)=data_entry!$A$1
y=y+1

cells(x,y) = "id"
cells(x+1,y)=":"
etc

I have to lookup one of my macros on how to get some of this information that comes next





Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources