I am displaying cells in textboxes depending on the results of a listbox selection. I was wondering if there is code for making the textbox names variable (so I could just loop through the offsets and textbox names)
the code below works but I would like to make it more versatile and shorten it.
There are other textboxes on the page.
Sub detailfill() ....
I have a workbook with 31 sheets. Each sheet has 15 textbox button that call macros.
I would like to hide 1 textbox on all sheets until another macro is called. I know how to get the textbox names to be able to hide them, But because these text boxes were copied most of the are the same name, however on severl sheets they are different names "Textbox 4 on most sheets but it could be textbox 34 on others. The ones I want to hide all have the same text label. Is it possible to get VBA to return the label text.
Having a bit of trouble trying to get excel to pick up text in one sheet (sheet 2) and populate cells in another (sheet 1) if the row (row 1) labels and columns (column a) in both sheets match. hope that makes sense? I've tried googling this to no avail, i've also tried index-match however i keep getting errors.
View 3 Replies View RelatedI 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 a listbox that is currently populated via the following code, designed to give a unique list with no repeats.
Code:
Sub UserForm_Initialize()Dim UniqueList() As String
Dim X As Long, y As Long
Dim r As Range, rAll As Range[code].....
This code works fine. NOW, what I need to do, is set individual labels, based on the values in the listbox, BUT, here is the kicker - I don't want to select any of the listbox values. I know there will be seven rows in the listbox that correspond with seven labels.
What I nede to do, is set the first label.caption as the first row in the listbox. The second label as the second row, etc. The listbox only has one column, and I have tried the following code, but it doesnt work.
Code:
With UserForm2 .Label1.Caption = .lbxParalegal.List(0, 1)
End With
I'm amtepting to populate some labels from some predefined strings based on the scrollbar value. I have these codes:
Option Explicit
Public Meddelande1 As String, Meddelande2 As String, Meddelande3 As String, Meddelande4 As String, Meddelande5 As String, Meddelande6 As String, Meddelande7 As String, Meddelande8 As String, Meddelande9 As String
Private Sub UserForm_Initialize()
On Error Resume Next
Workbooks("Kontrollsystemet.xls").Close SaveChanges:=False
Application. ScreenUpdating = False
Workbooks.Open "V:allaBeredningKontrollsystemetKontrollsystemet.xls", ReadOnly:=True
Sheets("Meddelanden").Activate
Meddelande1 = Range("B2").Text
Meddelande2 = Range("B3").Text....................
I'd like to: Display the contents of a group of cells in a userform's label's caption. The columns containing the cell's which contents would be displayed are fixed (do not change). The rows will change. I need to reference the Ar, Br and Cr columns (where r is for row) of whichever column I am working on at a given moment.
View 4 Replies View RelatedI 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 RelatedRight now I have this:
Screen Shot 2013-11-29 at 1.02.47 PM.jpg
How can I put another label under each bar (NOT outside end or inside end or any additional label. This is an actual label? For example, under the first blue bar labeled 56.7%, I also want to put N = 50 below the X-axis, above "Optimal." I also want to put N = 25 for the red bar immediately next to it labeled at 75.7%.
This is not a label, as it is below the X-axis.
An example of this can be found here (scroll down for second chart)
[URL]
I would like for the If statment to hide multiple labels and buttons. How can I add more than one condition after Else:?
View 3 Replies View RelatedMy friend has set up an excel workbook with a sheet for every month (labelled Jan graphs, Feb graphs, Mar graphs etc) which has 7 pie charts per sheet. The problem is the pie charts are showing the 0% fields and the data labels are overlapping. I have found a macro that gets rid of the 0% labels but I need to repeat the macro for all the 7 charts on each sheet and all 12 worksheets for each month of the year. I am very new to VBA and coding of any type and have looked but can't find the solution.
The macro I am currently using for "Chart1" on "Jan graphs" is below. The charts are labelled Chart1, Chart2, Chart3 etc.
Sub ClearLabels()
Worksheets("Jan graphs").ChartObjects("Chart 1").Chart.ApplyDataLabels _
Type:=xlShowLabelAndPercent
For Each x In Worksheets("Jan graphs").ChartObjects("Chart 1"). _
Chart.SeriesCollection(1).Points
Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.
In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.
CURRENT SHEET
ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89PENS ABC STATIONERY CO LLC [code]....
The attached example file may be a better alternative to view this problem.
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
I have column in my table that calculates time in hours between now and ticket open date (ticket open date in B1)
so, in C1 i have following formula:
=(now()-B1)*24
Results are in hours, such as 15, 25, 32, 65 etc
In pivot table, I would like to see how many tickets with values of less than 24 or more than 48, etc
My available columns are: Region, Ticket#, AgingOpenHr(this new column).
I tried it different ways, and I cannot get it Less Than and Greater Than work under Values, works if I select grater than or less than in Label, but label does not produce consistent numbers.
I would like a macro that select all cells based on multiple criteria. It's a big sheet, but I've made it smaller in the following example.
In row 1, there are 2 headers, in B1 (electricity) and C1 (gas) In column A, there are 3 houses, in A2, A6 and A10. The blocks of cells in between (e.g. B2:B5) have 2 headers, 'period' in B2, and 'account number' in B4. B3 and B5 have a list of options in them.
I want to select cells that meet certain criteria. Either electricity or gas, and either period or account number. So, if in cell D1 I choose electricity and in D2 period, I would like to select B3, B7 and B11. If I choose gas and account number, I wish to select C5, C9 and C13.
"Is there a way to make values in a cell that are comma delimited (ex. cat, dog, fish) became separate labels in a pivot table instead of getting labels like 'cat, dog', 'fish, cat', 'cat, dog, fish' which represents the exact value in the cells.
The only real option I can think of would be to make an extra rows with the same data for each pet type. For example if the pets were 'cat, dog', there would be two almost identical rows except one would have 'cat' and the other 'dog'."
I have made a vba setup which can solve the problem. But it is pretty complicated and unstable. I need a solution which is not vba based. ~Or a least a very simple code!
how to make a formula using a column label to count all cells that fall in a given date range. Example, in the attached sheet I would like to make a formula using the column heading "Dismantled" to count all cells that contain dates ranging from 10/13/08 through 10/19/08 inclusively.
View 5 Replies View RelatedI have created a UserForm that has a ComboBox and depending on the number selected I want it to show that number of Labels/TextBoxes...
So if I select "0" nothing is shown, if I select "1" one set of Labels/TextBoxes is shown, select "2" and two sets of Labels/TextBoxes are shown... but also if I have selected "2" and then select "1" I want the second set to be hidden again...
Also I know I should have renamed the Label/TexBoxes to make it easer but I was adding things and making it up as I went along...
I'm using Excel 2010 on windows 7.
Code:
Sub UnHide_NewRoutings()
If (Engineering.ComboBox2.value) = "0" Then
Engineering.Label4.Visible = False
Engineering.TextBox5.Visible = False
Engineering.Label9.Visible = False
Engineering.TextBox9.Visible = False
[Code] ..........
I’m trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. I’ve searched and found similar forums here, but since I’m ignorant in VBA code, I couldn’t manage to make adjustments. So if you can help me with the code, I would really appreciate it!
Condition and Results required would be:
IF column C = “S” than on Column B = color cell light blue with white border
IF column C = “P” than on Column B = color cell Green with white border
IF column C = “A” than on Column B = color cell Yellow with white border
IF column C = “L” than on Column B = color cell Red with white border
IF column C = “C” than on Column B = color cell Dark Blue with white border
IF column C = “ ” than on Column B = color cell white
IF column C = “V” and has white background than on Column B = color cell white
IF column C = “V” and has a Dark Grey background than on Column B = color cell Dark Grey
I don’t know if the last two are feasible.
I'm working on a workbook to track staffing patterns. I have two userforms included in my workbook. The first userform (userForm2) initiates upon opening the workbook. It's intended to allow the user to enter a date range and an office location for the report. The second userform (userform1) initiates when a command button (Weekly Summary) is clicked. I've linked texts boxes in userForm2 to cells in a hidden worksheet; this is where I'm holding the dates and office location until userform1 is initiated. I also have labels in userform1 linked to the same cells in the hidden worksheet so that when userform1 is initiated the office and date range appear at the top of userform1.
Here's the problem, when I click command button "Weekly Summary" the office location shows up perfectly however, the labels I have linked to cells in the hidden worksheet that contain dates do not update (i.e. they show the dates that were previously in those specific cells. I have to close userform1 and re-open it to get the dates to update.
I need the user to be able to choose a date range and office location when they enter the workbook. Then, I need userform1 to show the date range and office location (without having to open it, close it and re-open it) that the user chose on opening the workbook.
On a sheet called, "Details", I have a pivot table that has three fields in the column area, Calendar type, Description, and Dates. I want to filter the pivot table based on a label filter in the Dates column. The filter should be between two dates (in D4 and D5) that are entered on another sheet and passed to the Details sheet through formulas in cells D4 and D5.
I have attached the following code to a button on a different sheet.
The code successfully filters for the employee name (which is a report filter in the pivot table) which is in a named cell.
I am having trouble with passing the start and end dates to my pivot table filter. I do not get any errors, the filter is simply blank.
Private Sub CommandButton3_Click()
Sheets("Details").Select
Dim pt As PivotTable
Dim Employee As String
Dim SDate As String
Dim EDate As String
[Code]...
I want the 'Model Number' to automatically be picked based on the numbers entered into Sheet1 B1 & B2. The model numbers are listed on Sheet2.
View 4 Replies View RelatedHow multiple cells can be formatted based on one cell value. See the screenshot attached to understand what I am trying to achieve. I am very new to excel formatting, vba etc.
ExcelFormattinQs.jpg‎
In column A i have values that may repeat throughout Column A, then next to the value in ColB, there is a sequence number 1,2, 3 for each of the same value in ColA. Then the Text in ColC that i need.
What i Need is to be able to search ColA, Then get the correct Sequence number from ColB, and place the contents in ColC into ColD or into a new worksheet if necessary.
I'm trying to remove cells that contain certain suffixs. Here is the code I'm trying to use:
Code:
For i = 7 To 2000
If Cells(i, 7) = "IV," Or Cells(i, 7) = "Jr," Or Cells(i, 7) = "III,"
Then Cells(i, 7).DELETE Shift:=xlToLeftEnd IfNext i
but it does absolutely nothing. I've used this for similar macros in the past and it worked just fine.
in the lookup function listed below, it only produces a result for the first exact macth. well, what if the data i am searching on could result in multiple results?
i would like advise on how to modify this formula to bring back the 2nd, 3rd, 4th, etc instance of a match in the lookup array.
the original formula , i found on this site, allows you to combine multiple cells to create a unique reference, and then search in another table containing columns of those combined cells, and results in the intersection of a row and column and that is the data you want returned ....
kind of like a hlookup, but you can use one or more cells to create the unique lookup, and bring back whatever column you want (where as a hlookup is limited to searching the left most column and bring back a column to the left)
here is the formula:
{=INDEX(data to retrieve,MATCH(cell1&cell2,table to find cell1 in&table to find cell2 in,0))}
"data to retrieve" is an array of one column containing the data you want to retrieve
"cell1&cell2" is a combination of cells to make the unique lookup (this could just be one cell if you wanted)
"table to find cell1 in&table to find cell2 in" is a table array to look in to find the look up value
I need to have multiple ranges count based on several criteria. The hitch is that needs to count if one or more criteria is met not all. I tried using a COUNTIFS however that seems to evaluate based on AND not OR.
Is there a nested COUNTIFS or similar function that would work?
