+ Reply to Thread
Results 1 to 29 of 29

trying to convert string variable to barcode font and store as new string var

  1. #1
    Registered User
    Join Date
    04-26-2018
    Location
    buffalo
    MS-Off Ver
    2013
    Posts
    12

    trying to convert string variable to barcode font and store as new string var

    So I am working on creating an automated label generator, need partnumber, description and the partnumber as a barcode on each label. I have everything except the barcode. here is what I have right now:

    Barcode = partnum Font.FontType = "Free 3 of 9 Extended"

    so my thought process is take the current string var (which is changing as the macro progresses through the list) and change the font to the barcode and then store as a new variable.

    Thanks, can provide additional code if required.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    Hi, welcome to the forum
    The variable will NEVER be the barcode, it's the format
    Try this, set the value of the barcode in cell B1 and cell B2 but set the font in cell B2 to that font name and that should do it.

    Please Login or Register  to view this content.
    or something similar, play around with this
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    04-26-2018
    Location
    buffalo
    MS-Off Ver
    2013
    Posts
    12

    Re: trying to convert string variable to barcode font and store as new string var

    thanks for the welcome, given that info im gonna have to figure out how to code multiple font types per cell

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    Nope, just one font type for the cell that holds the barcode
    Depending on the type of barcode, you will have to get that font and install it (on your system)

  5. #5
    Registered User
    Join Date
    04-26-2018
    Location
    buffalo
    MS-Off Ver
    2013
    Posts
    12

    Re: trying to convert string variable to barcode font and store as new string var

    well the current plan is to have all the information contained in one cell

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    And can you show me (in a file) what you have in mind?
    How will the cell look? Height, Width?
    Does this mean that you want the text in the cell to display different font styles?

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    Something like this?
    Attached Images Attached Images

  8. #8
    Registered User
    Join Date
    04-26-2018
    Location
    buffalo
    MS-Off Ver
    2013
    Posts
    12

    Re: trying to convert string variable to barcode font and store as new string var

    yes exactly, I need to make sure I have permission to share the file before I do, but that is what I am trying to accomplish. (the info will be pulled from a greater database based on a selection of partnumbers)
    Attached Files Attached Files
    Last edited by mike_tako; 05-01-2018 at 03:43 PM.

  9. #9
    Registered User
    Join Date
    04-26-2018
    Location
    buffalo
    MS-Off Ver
    2013
    Posts
    12

    Re: trying to convert string variable to barcode font and store as new string var

    I have attached the file to my last response

  10. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    I'll download the file and see if I can make it work
    It's your responsibility to make sure that the font is installed on the systems that are going to use the file

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    Will the cell content always be the same?
    First row of text the text to be shown as barcode with the other text below?
    Is it possible to manipulate the output by placing a * before and after the code?
    That is what they advice on the site with the font but, I'll test and write simple UDF so it can be done.


    *12C15-14*

    washer flat #10 guide scrwes

  12. #12
    Registered User
    Join Date
    04-26-2018
    Location
    buffalo
    MS-Off Ver
    2013
    Posts
    12

    Re: trying to convert string variable to barcode font and store as new string var

    are you able to access the macro ive written so far? (Create_Labels). The cell content consists of three things: 1st line - part number in regular font ie 12C15-14, 2nd line - part description in regular font, 3rd line - part number from 1st line but as a barcode. So this format is the same for each cell, but of course the values must change with each cell. As of right now the * method does not seem viable since the part number has to exist as both font types, not sure however.

  13. #13
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    It seems the * is not necessary

    The macro works
    You can call the macro after you run the create labels macro and it does the job
    The font size for the barcode font is set to 40 but you can modify that in the macro
    Attached Files Attached Files

  14. #14
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    I checked you macro and saw what you really wanted
    Will rewrite the macro so that it's taken in with the Create label and that the partnumber shows on top, the description and then the barcode.
    I'll upload the modified file later today, the idea is the same the position isn't
    Sorry for that.

  15. #15
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    Okay,

    I set the fontsize for the barcode to 20 it's a constant you can modify in the CreateLabel module.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    04-26-2018
    Location
    buffalo
    MS-Off Ver
    2013
    Posts
    12

    Re: trying to convert string variable to barcode font and store as new string var

    incredible, thank you SO much!

  17. #17
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    No, not icredible, just VBA

    You're welcome, I was looking to do something for Sheet1 but is password protected and I did not feel like throwing my unprotect code at it

  18. #18
    Registered User
    Join Date
    04-26-2018
    Location
    buffalo
    MS-Off Ver
    2013
    Posts
    12

    Re: trying to convert string variable to barcode font and store as new string var

    what did you have in mind?

  19. #19
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    Well, you got two buttons on that sheet, maybe more but that you deleted for the demo.
    The list is long and if you scroll down the list there is a method to always keep the buttons in view, they 'float' with you.

  20. #20
    Registered User
    Join Date
    04-26-2018
    Location
    buffalo
    MS-Off Ver
    2013
    Posts
    12

    Re: trying to convert string variable to barcode font and store as new string var

    gotcha, cool feature for sure!

  21. #21
    Registered User
    Join Date
    04-26-2018
    Location
    buffalo
    MS-Off Ver
    2013
    Posts
    12

    Re: trying to convert string variable to barcode font and store as new string var

    hopefully you see this, but im looking to add a part image to the right side of the label. The code I am considering right now is as follows:

    Please Login or Register  to view this content.
    So the idea is past it in at a specific size from a different file as the name changes and not have it interfere with the text already in the cell

  22. #22
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    You say you are considering, well did you test anything?
    I suggest you attach one or two of the image files so I can test.
    You could also record a macro to insert a picture in cell and resize it accordingly, then you can see how that works in the code.
    You have cells which have fixed dimension so I suggest you work something out first and then attach THAT file too and I'll see what I can do.
    It's good practice to try yourself, that's the best way to learn and not become dependent of others.

  23. #23
    Registered User
    Join Date
    04-26-2018
    Location
    buffalo
    MS-Off Ver
    2013
    Posts
    12

    Re: trying to convert string variable to barcode font and store as new string var

    yes, don't want to be clinging on. So i have done quite a bit of testing with some interesting results. first of all, ive only been using one image file for the sake of simplicity, next, i had some success for a bit in getting the images to populate, although not for the full extent of the parts, and not in the format that I wanted (code was working, just couldnt figure out how to move the images to the right side of the cell). After some success I now get an error saying excel cannot set the left property of the range class. The file/image size is not an issue as the code successfully resizes it when it works, i will attach the full code, its saying the image file is invalid...
    Attached Files Attached Files

  24. #24
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    I just recorded a macro and then edited it to resize and position the image on the right side of the cell
    Please Login or Register  to view this content.
    The syntax is InsertPicture cRange:=Range("A2") or any range and it works
    Attached Images Attached Images

  25. #25
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    You can place this file in the images folder and use it if the image does not exist to insert this one

    I checked your code and instead of placing the picPath as a variable in the code make it Public so you can change it if required and do not have to modify the module code

    Please Login or Register  to view this content.
    Attached Images Attached Images

  26. #26
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    If you make sure that in noimage.jpg is in the picture folder then this code will work

    You will however have to check and allow the image extensions to be a tif, jpg, png, bmp etc. and choose the appropriate



    Please Login or Register  to view this content.

  27. #27
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    Hi, just curious to know, did you manage with the code I posted?

  28. #28
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    I have attached the updated file.
    Hope it works for you, the image file may be any type, .jpg, .png, .bm etc , just use the partnum to find it and it it's present with any of the extensions it is used.
    Make sure the NOIMAGE.jpg I posted is in that same images folder if you want that to show if no picture file is present.
    Attached Files Attached Files

  29. #29
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: trying to convert string variable to barcode font and store as new string var

    You will have to see what you're going to do with the spare text, it falls behind the picture if its a long text.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. store cells value as a string variable
    By sebdor in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-02-2014, 03:31 PM
  2. Replies: 1
    Last Post: 01-30-2013, 08:30 AM
  3. Transferring a dynamically named string variable to another string variable.
    By Concorde Rules in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-09-2012, 04:08 AM
  4. is it possible to convert string to variable with the same name?
    By bagullo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-30-2012, 05:39 AM
  5. Change font color of a string that is a variable
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-22-2011, 08:53 AM
  6. How do you convert a string into a variable name?
    By sauerj in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-22-2010, 11:58 PM
  7. Convert String to a Worksheet type variable
    By cd in forum Excel General
    Replies: 1
    Last Post: 10-12-2005, 08:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1