(Spoiler, long post)
Hello all,
I am relatively uneducated when it comes to VBA, but I have muddled my way into a semi-functioning project that until recently worked fine. Let me begin with a little background. I work in Security and we moved to a different Asset Tracking system a little bit ago. Unfortunately, this system did not really provide us with tags that were comparable to the ones we were using, and as such we had decided to make our own style.
I came up with a template based on the design of our old tags in Excel. The original template required the user to copy and paste each tag style (as different assets are color coded) and then manually enter the pertinent information. As you can imagine, this was time consuming and inefficient. So I decided to try to automate as much of the tag generation as possible. I had dabbled a little in VBA (Several of our regularly used forms are in excel, and we have incorporated some simple macros to make them more user friendly) but had never really done any big projects.
To start off I searched the internet to find snippets of code that would do some of what I was looking for. I posted on the Microsoft forums asking about VBA being able to incorporate images from a file (The code I was given in response has worked great for the most part, but sometimes...). I have pretty much frankensteined this project together with various snippets of VBA and use of the record function. The code has changed since I started, and with that come new problems.
The template I have created is supposed to be split into three parts: Mobile Assets, Fixed Assets, and a simple form that I can use to print off just barcodes. This brings me to the first major issue that I’ve been having lately. I seem to only be able to have so many barcode images. I.E. When I reach the limit, and then add a new one, one of the prior barcodes disappears. This has stumped me, and forced me to leave out the simple tag sheet until I can figure it out. However, this issue is also showing up in my other sheets. I think I somehow fixed my mobile sheet, but the fixed sheet was always losing the last tag on the top row, or the first tag on the second row. After about a month of trying to figure it out I decided to redo the VBA code. This was also prompted by a need to use named ranges for ease of troubleshooting (I.E. Fix one tag’s worth of code, copy it with a different range ID, and move on).
At this point I have redone the code, but I am running into a far more irritating problem. Some of the formulas that are called (VLOOKUP) are not actually working, and the cell ranges are not staying unlocked. This is an issue, as I have only 8 cell ranges unlocked, so the formulas aren’t inadvertently erased or altered. I can’t seem to find out what the problem is for either issue, and so I’ve turned to this forum, before I start implanting my head into my desk. To be perfectly honest, I’m also under a potential deadline, in which I pass the template to my supervisor for use, and show him how to use it.
My questions are as follows:
1) First and foremost, can anybody help identify the problem that is preventing my formulas from working?
2) Same with the issue of the cells being locked. This is defeating the purpose of having the sheet protected.
3) Next, I’d really like to know if the issue of limited images (barcodes) can be identified so I can fix it.
4) My final request is about optimization. I’ve tried what I can to squish, condense, and otherwise shortcut it, but again, I’m not really experienced at VBA. Any more tips, tricks, and such would be appreciated J
Sort of tying in to the optimization help, does anyone know if it’s ok to use the Application.Run “blah” to call private code in a different module, or if there’s a better method?
Below is a link to my dummy file (edited to reduce database size and remove some company info) I'm providing the file, as there are several relatively large chunks of code, and it's easier to see it as it's supposed to be.
https://drive.google.com/file/d/0Byf55gi6jLW1UmV1RmpjdWRHWUU/view?usp=sharing
If I’ve missed anything, I apologize. I’ve tried to get this done on my own, and an banging my head against a wall now.
Hello all,
I am relatively uneducated when it comes to VBA, but I have muddled my way into a semi-functioning project that until recently worked fine. Let me begin with a little background. I work in Security and we moved to a different Asset Tracking system a little bit ago. Unfortunately, this system did not really provide us with tags that were comparable to the ones we were using, and as such we had decided to make our own style.
I came up with a template based on the design of our old tags in Excel. The original template required the user to copy and paste each tag style (as different assets are color coded) and then manually enter the pertinent information. As you can imagine, this was time consuming and inefficient. So I decided to try to automate as much of the tag generation as possible. I had dabbled a little in VBA (Several of our regularly used forms are in excel, and we have incorporated some simple macros to make them more user friendly) but had never really done any big projects.
To start off I searched the internet to find snippets of code that would do some of what I was looking for. I posted on the Microsoft forums asking about VBA being able to incorporate images from a file (The code I was given in response has worked great for the most part, but sometimes...). I have pretty much frankensteined this project together with various snippets of VBA and use of the record function. The code has changed since I started, and with that come new problems.
The template I have created is supposed to be split into three parts: Mobile Assets, Fixed Assets, and a simple form that I can use to print off just barcodes. This brings me to the first major issue that I’ve been having lately. I seem to only be able to have so many barcode images. I.E. When I reach the limit, and then add a new one, one of the prior barcodes disappears. This has stumped me, and forced me to leave out the simple tag sheet until I can figure it out. However, this issue is also showing up in my other sheets. I think I somehow fixed my mobile sheet, but the fixed sheet was always losing the last tag on the top row, or the first tag on the second row. After about a month of trying to figure it out I decided to redo the VBA code. This was also prompted by a need to use named ranges for ease of troubleshooting (I.E. Fix one tag’s worth of code, copy it with a different range ID, and move on).
At this point I have redone the code, but I am running into a far more irritating problem. Some of the formulas that are called (VLOOKUP) are not actually working, and the cell ranges are not staying unlocked. This is an issue, as I have only 8 cell ranges unlocked, so the formulas aren’t inadvertently erased or altered. I can’t seem to find out what the problem is for either issue, and so I’ve turned to this forum, before I start implanting my head into my desk. To be perfectly honest, I’m also under a potential deadline, in which I pass the template to my supervisor for use, and show him how to use it.
My questions are as follows:
1) First and foremost, can anybody help identify the problem that is preventing my formulas from working?
2) Same with the issue of the cells being locked. This is defeating the purpose of having the sheet protected.
3) Next, I’d really like to know if the issue of limited images (barcodes) can be identified so I can fix it.
4) My final request is about optimization. I’ve tried what I can to squish, condense, and otherwise shortcut it, but again, I’m not really experienced at VBA. Any more tips, tricks, and such would be appreciated J
Sort of tying in to the optimization help, does anyone know if it’s ok to use the Application.Run “blah” to call private code in a different module, or if there’s a better method?
Below is a link to my dummy file (edited to reduce database size and remove some company info) I'm providing the file, as there are several relatively large chunks of code, and it's easier to see it as it's supposed to be.
https://drive.google.com/file/d/0Byf55gi6jLW1UmV1RmpjdWRHWUU/view?usp=sharing
If I’ve missed anything, I apologize. I’ve tried to get this done on my own, and an banging my head against a wall now.
