to print each row of a spreadsheet as a separate label and have a feeling that this possible using macros but i have very limited knowledge of them. The number of times I need to print this label is contained in a cell in each row.
I know that i can go into word and use mail merge to produce labels but i would like something altogther more automated to remove the possibility of human error.
i have a column with numbers in, in numerical order, however some cells are null. ie.
1
2
3
4
..
etc. I need to put paste images from a folder next to these numbers. (1.bmp next to '1' etc) and i need to leave the empty cells in. This code is sort of right..
Sub aids()
Dim strPath As String
Dim strFile As String
Dim lngRow As Long
Dim objPic As Object
Dim sngMaxWidth As Single
On Error Resume Next
lngRow = 2
strPath = "C:images"
strFile = Dir(strPath & "*.bmp")
With ActiveSheet
Do While strFile <> ""..........
Since upgrading to Windows 2007 (I was already using Excel 2007) I am having issues with the content in the cells on the worksheet not appearing the same on Print Preview and when I print. On the worksheet the cell show to be at the best fit both horizontally and vertically. When I look at the contents under print preview, the contents are squashed from the top and cut off from the left. This happens whether I have the format in Top or Central align and is even worse if I use Bottom align. It is also somewhat worse if I have thickened boarders.
I am using TrueType Fonts.
I have a database of around 300 names and addresses from which I need to print labels .....
If not what is the best way to do it. Can this file be converted to a doc or mdb file and then mail merge in MS Word. Could somebody please reply at the earliest as I have very little time to create these labels ?
We have a an existing spreadsheet which uses a query from Excel to Sage Line50 Accounting software to create labels for stock items. The number of times a label is printed is dictated by the number of items that are required for a particular sales order.
For example -
1006abc 100 No.
2003abc 20 No.
The program would produce 100 labels for item code 1006abc, and would produce 20 labels for item number 2003abc.
However we have recently acquired a heavy duty industrial printer which prints to labels on a continuous roll and I am unable to alter the spreadsheet to work with the new printer layout.
I have attached the spreadsheet and, I hope, the VBA code. PLease also see this link to my previous post - Print Labels Dependant on Content
I have a form that users will fill in, and I need to print a set number of labels (all identical) based on the info that is entered.
I have the form set up with data validation and indirect references(Lists on a separate sheet)
Example...
User enters in:
Customer name
Product name
Lot #
PO#
Date made
Number of items
From there I have a separate set of cells that reference the above info formated to how I want the label to appear when printed.
Now, I need to repeat that label the set number of times (# entered in as "number if items") in a label sort of format (not sure if a mail merge will work for this??)
Then, print off and stick identical labels onto each part that is manufactured per PO/Date etc.
How do I print multiple inventory labels based on our inventory levels?
For example:
Item No 1000
Descr Window
Customer Taylor
PO 9001
Quantity 10
Item No 1010
Descr Door
Customer Jones
PO 9011
Quantity 35
I want to print 10 labels with the info from Item No 1000 and 35 labels from Item No 1010????
I have an export from a database that I'm bringing into Excel 2010 of about 30K records. Data points are recorded numerically and I have their associated text "value label" (what it would be called in STATA, for example, not sure what it's called in Excel). I want to create various charts/pivot tables with the data and want the labels to be the text label, not the number.
For example, variable ASSIGNMENT has the following possibilities:
1
2
3
4
Here's what each of those "mean" (I have this in another table):
1 - Sick
2 - Overtime
3 - Court
4 - Present
How do I create a chart or pivot table where the labels are "sick", "overtime", etc., and not "1", "2", "3", "4"?
I have a UserForm and what I'm trying too do is change the color property of all the labels on the form simultaneously.
View 9 Replies View RelatedI have a worksheet that is just a list of items with a location listed next to each item. I took that list and made it into a pivot table and would like it to have it list the items with each location it is in displayed across. I have already set it to tabular form in the pivot table options, but if there are multiple locations it lists them vertically and I want them list horizontally for printing purposes. I have attached a screen shot to explain.
View 1 Replies View RelatedI am using VBA to create a word document (.docx). This word document contains plain text content controls as well as picture content controls. I then use VBA to automatically select a picture based on the code below
Code:
Set oCC = Word.ActiveDocument.SelectContentControlsByTitle("TabPic").Item(1)
On Error GoTo TabErrorHandler
oCC.Range.InlineShapes.AddPicture Filename:="X:XFERANDREW-TDCD " & LblVL &
[Code].....
After the document has been closed down I try to open it again and I am told "The file cannot be opened because there are problems with the contents."
When I click details it says "Unspecified error" and "Location: Part: /word/document.xml, Line: 2, Column: 0"
If I click ok it says "Word found unreadable content in "". Do you want to recover the contents of this document? If you turst the source of this document, click Yes.
Clicking Yes opens the document with all the contents and it is now renamed to Document 1. If I click no it does not open.
I use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell
Sub COMMENT()
Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?
how to combine the content of 2 cells into one cell and have the information separated by a comma.
For example:
CELL 1:
Software 1
CELL 2:
Spreadsheet Software
The desired results is:
CELL 3:
Software 1, Spreadsheet Software
Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.
Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"
I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
Basically what i need to do is create a validation list, the contents of which will be dependant on the entry in another cell. The catch is that the other cell contains an "if" formula.
eg.
A1: 10
B1: =if(A1=10;"A";if(A1=11;"B";if(A1=12;"C";"Invalid Entry"))) ....
I want to sum a range of cells but exclude any that are in a particular colour (in this case red).
View 3 Replies View Relatedin cell d1 i have
hk/q46559
in cell i1 i need to return whatever is before the "/"
in this case hk
and in cell i2 i need to return the number after the q
in this case 46559
on sheet1 which is called working sheet and cell d25 is asking what type of goods been sold
i have several different sorts"
ie
s25
s28
s50
s69
s70
s82
abd
t70
t140
i want the spreadsheet to look up somewhere on a sheet called delivery all the components that could be used for that type
so i guess i need to name define?
ie if s25 is selected
on delivery sheet in a20 it would then list all the components underneath each other.
I have a worksheet with 4 drop boxes that have the following options in order: 2,28,10,24. My main concern is to make the last drop box depend on the third: so the one with 10 listings will determine which of the 24 will appear according to selection. The other two are not necessarily needed to be dependent although, they will be pulling different data according to the selections. How to make the last two be dependent? The combo box selections will determine the values pulled for four different time frames.
View 4 Replies View RelatedI'm creating a spreadsheet to track orders as part of a project, the spreadsheet currently lists all the different options that can be ordered, it then lists all parts of the order, installation & setup etc.
However it is likely that not all of the options will be used.
What I would like to do is create a few drop down boxes for each option, if 'yes' is selected then the order tracking for that option shows below.
Example, the wants 'internet' so 'yes' is selected for internet using the drop down at the top of the page. Further down the page all the order tracking info is shown for internet. If 'no' is selected nothing is shown for interent.
In the file I have attached, I need to work out how to give the final total (cell G27) inclusive of VAT, dependent on the value assigned in cells K17-K26, which are explained in the list starting cell B29.
At the moment the value given is net of VAT, and I need the total figure in G27 to recognise that the first item needs to be at 15%, the second at 5% and the 3rd at 0%.
I have a Filter set up across a few columns of data. Lets say one of the columns is called "Hours", and another is called "User". There is another cell called "Totals" outside of the Filter.
What I would like to have is a self-updating Totals cell that will Sum the total number of Hours for whatever the Filter is currently showing.
Examples:
If I set the Filter to show only UserA's data, the Totals cell will Sum the Hours for UserA.
If I set the Filter to show All Users with Hours greater than 2, the Totals cell will Sum all the Hours greater than 2.
If I set the Filter to show UserB and UserC and Hours less than 5...well you get the idea.
Can I write a function that loops through the data that meets the Filter's criteria? Do I need to create a macro to update the Totals cell each time the Filter is changed? Is there an easier way that I don't know about?
I want to show the number of weeks remaining from the sum of a column BUT I want it to distinguish between the entry in another column.
See below.
Columns B to L represent a number of weeks
EG1
Column C: Year 1 has a set amount of 47 and C156 displays this.
C157 should display the amount of weeks in Column C but only when a number "1" appears in Column "Y" C158 displays the total amount remaining
EG2
Column H: Year 2 has a set amount of 38 and H160 displays this.
H161 should display the amount of weeks in Column C but only when a number "2" appears in Column "Y" C162 displays the total amount remaining
so basically, im trying to find a way to get cell G3 to have Value X dependant on value from Cell G2. e.g. if cell g2 is 8 and 9, g3=10 if g2 is 11, g3=20 and so on. i dont mind having to add a preset table of values if we need, but it would be set on sheet 2.
EDIT
Value from cell G2 will always be between 4 and 20, set by the user. what i want is that each of those values gives G3 a pre-planned value.
if g2 has value 4. g3 value would be -5
if g2 has value 5. g3 value would be 0
if g2 has value 6. g3 value would be 5
if g2 has value 7. g3 value would be 5
if g2 has value 8. g3 value would be 10
if g2 has value 9. g3 value would be 10
if g2 has value 10. g3 value would be 15
if g2 has value 11. g3 value would be 20
if g2 has value 12. g3 value would be 20
if g2 has value 13. g3 value would be 25
and so on until it reaches g2=20
i was thinking of setting a table on sheet 2 for the g3 values, and get an IF function to do something like IF g2 = a then x, if g2 = b then y etc etc. im just not sure how it would work, unless an INDEX function would work better?
I've been making a userform to enter data into a list of clients for our school counsellor. I've been using date picker to select the date, which works fine, but is a bit fiddly for a novice user (which the counsellor is) to jump down years.
My half-solution has been to change the data to a very rough guess of their data (so it's no more than a year or so away) - by taking their school year (years 7 to 11) in one combo box, adding 5, multiplying by 365 and taking the that number of days from the current date.
Private Sub CmbYear_Change()
DTPicker1.Value = Date - ((5 + CmbYear.Value) * 365)
End Sub
And although it changes the content of the datepicker date, I get an error when I click my ADD button.
Run-time Error '13':
Type Mismatch
And here's the code for my ADD button.
Private Sub CmdAdd_Click()
ActiveWorkbook.Sheets("Clients").Activate
Range("A3").Select
Do
If IsEmpty(ActiveCell) = False Then
I am trying to make an Excel sheet with a frontpage sheet and a data sheet. In the data sheet I have several columns with data and then in the end a cloumn with pictures. On the front page sheet I can easily with VLOOKUP formulas transfer the desired data from the data sheet to the front page sheet but I cannot seem to figure out how to copy the picture. When I try to copy the cell with the picture in it, the formula only copies the cell value, which is "0" because the only thing which is in the cell is the picture. how to copy a given picture from one sheet to another based on the information in another cell like a "Vlookup" formula?
View 3 Replies View RelatedI have
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
With Target
Select Case .Value
Case "(None)": .Interior.ColorIndex = Null
Case "One": .Interior.ColorIndex = 38
Case "Two": .Interior.ColorIndex = 18
Case "Three": .Interior.ColorIndex = 35
Case Else: .Interior.ColorIndex = xlNone
End Select
End With
End If
End Sub
My problem is that columns A to H are yellow(36 I think) and are merged cells.
And Cells AW2 to BD2 are also yellow. The cells inbetween are white.
At the moment when the Cell value is "(None)" the cells turn white, I dont want this. I would like them to go to default.
what i need to do is display a list of items, with a dropdown box next to each item. the dropdown boxes are identical. for each item in the list (e.g. each musician), the user should be able to select an item from the box ("percussion", "horn", "string").
my problem is that the number of items in the list is not set, so the list of name labels, dropdown boxes, etc needs to be generated dynamically. is it possible to generate a list of labels/dropdown boxes using a loop? (as i understand it, this would involve writing code to write code.)
I have a spreadsheet where I would like the rows to change colour dependant on value input to certain cells, i.e. if a any value other than a zero put into a cell it changes to green, if a zero put into a cell it changes to grey, if a zero put in but a value put in another cell it changes to another colour, if values already in are taken out of other cells, it changes to yellow.
I've looked on FAQ and there are a lot of similar things but not quire wha I'm after and even thn I dont know how to use VBA etc to achieve.