I have numerous Text Boxes on my worksheet. The number of these objects vary, as many will be continually duplicated/ deleted.
The code below is a simple recording of selecting each object and scaling.
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes" ...
I have 35 text boxes to add to the bottom of a chart. Is there no way to mark multiple boxes and align them to the left or right or make them all the same height?
View 2 Replies View RelatedI have a workbook with 4 worksheet that store different type of data. It also has a userform that load at start of the application which is to search the data in the workbook. The userform has a combobox where the names of the sheets are stored. when the user selects say Sheet2 in the combobox, it enables the relevant textboxes on the userform and activates the worksheet at the change event. The userform has a search button that searches all the worksheets based on the text entered in a textbox.
The problem: how to search based on 1 textbox. What I want is: say for e.g the end-user selects sheet2 from the combobox, this intern enables 4 textboxes (Name, DOB, Nationality, ID #) on the userform. The end-user should have the liberty to enter data in 1 and/or any of the textboxes. The search should be performed, that if data is only in 1 of any of textboxes then give all rows that fit that criteria and display in a temp worksheet. if say the name and dob is filled by the user than what matches both should be displayed in a temp worksheet. if say dob, name and ID# given so the search button should narrow down to fit all 3 criteria and then display result in temp worksheet. As if mentioned data can be entered in either just 1 or any or all textboxes.
E.g. the worksheet is (Columns are Name, Nationality, DOB, ID#)
row 1 = name: Steven Martin, DOB: 27-may-1993, Nationality: Trinidad & Tobago, ID #: 1234567
row 2 = name: Gary Richards, DOB: 2-FEB-1993, Nationality: British, ID #: 456789
row 3 = name: David Cohen, DOB: 27-May 1993, Nationality: American, ID #: 98765
row 4 = name: Roberto McDonalds, DOB 21-Jul-1962, Nationality: British, ID # 654321
row 5= name: Gary Richards, DOB: 01-Dec-1978, Nationality: Australian, ID # 1234567
Now if the user enters only name as "Gary Richards" and search then row 2 and 5 should be displayed in a temp worksheet. if user enters name Roberto McDonald and ID# 1234567 then it should not display anything. if user enters DOB 27-may-1993 and nationality British and ID # 1234567 then as well shouldn't display anything and should a msgbox "no data found".
I have a rather large spreadsheet that takes a long time to calculate. One sheet has the data (about 2800 rows by 650 columns), with samples in rows and data for each sample in the columns. A second sheet has functions to group the data according to predefined patterns. That second spreadsheet is about 800 columns wide and as many rows as I need it to be, as the samples don't need to be analyzed all at once.
Ideally I would like to do all samples at once by making the second spreadsheet 2800 rows high, but the calculation time is just way too long. And there's the problem. The calculation time seems to increase exponentially with the number of rows I calculate at a time. I ran a few tests making it different sizes, and here are the results:
rows
time(seconds)
100
3.5
150
12
200
23
[Code]...
I tried both HLOOKUP and INDEX/MATCH functions and it didn't make a lot of difference. These numbers are for INDEX/MATCH.
Graphing these numbers show that it follows an exponential curve pretty closely. What is interesting is that the progress indicator at the bottom of the screen finishes in a few seconds, about 5 seconds for 350 rows, and I can see the new data after those few seconds. But then it sits and appears to do nothing for the rest of the time. So, for 350 rows, it looks to be done with the calculations within 5 seconds, but then it freezes for another 2 minutes with CPU usage maxed out before I can do anything with the program. That actual calculation time seems to scale linearly with the number of rows I calculate, but the time after it finishes calculating before it finishes whatever else it's doing scales exponentially.
The spreadsheet does have a macro and a couple of other sheets and a macro that do some later analysis and I wondered if that had something to do with it. To check that, I created a new spreadsheet and set it up the same as the original one, with the data in one spreadsheet and the INDEX/MATCH formulas in the other. I copied the data over, but setup the formulas new without copying/pasting anything. It didn't make any real difference.
I am trying to copy one worksheet using the "move/copy" function that is available when you right click a tab name and want to copy the worksheet in the same workbook
The steps I'm using are:
1. Right click the tab name
2. Select Move or Copy,
3. Select Create a Copy
4. Click OK
Doing all of the above does not work. When the new worksheet opens, all columns are the same width. It seems to be stuck on "autofit column width" setting of 8.5. The original worksheet is several columns wide all with different width settings.
I've also used Copy, Paste Special and selected column widths and that does not work either.
Finding the Column with the MAX Height in comparison with other Columns of UNEVEN Height
I need two things :
1. I have several columns starting from Column B till Column F, each column having values starting from the third ROW.
Ex:-
Lets say Column B contains two values in B3 and B4, Col C three values in C3 C4 and C5, Col D four values D3,D4,D5 and D6, Col E two values in E3 and E4, Column F five values F3,F4,F5,F6 and F7.
So, the answer is F3:F7.
2.I am using 10^{4,3,2,1,0} in a particular portion of a formula, Now the number of elements in the array or in the Curly Braces depends on the number of Columns filled from Column B as explained in the Point 1.
Now, since I have five columns under consideration I have this order as mentioned here {4,3,2,1,0} , I would like to know whether I could make this dynamic, as in if there were only four columns then this would be {3,2,1,0} and if more this array could self-fill and expand..
If that's possible, then how do we use it in the formula, Is it by the virtue of the INDIRECT function?
How do I get the actual worksheet height and width?
View 5 Replies View RelatedI used to have cells in the extremes of my worksheet filled which resulted in the scroll bars being stretched very thin. Since then, I am only using the top-left part of the worksheet but it is difficult to scroll through since the scroll bars are still stretched. How do I bring them back to normal?
View 5 Replies View RelatedHow can we convert time on a scale of 100 to a scale of 60?
View 3 Replies View RelatedI am using a ComboBox on a worksheet where the items in that box are pulled from Sheet1A:A. When the selection of the item in the comboBox is made, I would like several textboxes on that same page to be auto-populated based upon info derived from cells adjoining whatever row in Column A was chosen on Sheet 1.
For simplicity sake, let's say the range on Sheet 1 is A:H, and TextBox1 = "B", TextBox2= "C", etc.
so if,
A B C D
JOHN 15 DOGS BLUE
SUE 18 CATS RED
BOB 20 TURTLES YELLOW
When Combobox selection is JOHN, I want TextBox1= "15" TextBox2= "DOGS" TextBox3= "BLUE"
When I enter values in the userform textbox and press update button i need to copy whole row of data which is next to the textbox from userform to database sheet. (Copy only if data entered in the textbox).
Basically if textbox has a value in userform row 1 and it has a value copy it to database sheet if not skip and if value is in other texbox copy other rows.
The worksheet sample attached : example.xlsm
[URL] .....
i create new sheet on button click event using this code
Worksheets.Add before:=Sheets(Sheets.Count)
ActiveSheet.Range("A1").Value = TextBox1.Value
but now i want that when this new sheet will open there will be some labels, textboxes and buttons n etc will be appear on that sheet so how can i display these elements on new sheet dynamically ??
I want to enter a value in a textbox, search for it in an worksheet, and populate other textboxes with adjacent values if the value is found. Anyway, the problem is that if the value is not found, I get a debug error.
Dim test1
test1 = TextBox1.Value
Worksheets("data1").Activate
Find_Range(test1, Cells, xlFormulas, xlWhole).Select
TextBox2 = ActiveCell.Value
TextBox3 = ActiveCell.Offset(0, 1).Value
I'm sure there's far better code to do what I need, but I tried to keep it simple. With the above code, it only works if the value is found. If it's not, I get an error. So how do I make it so that if the value is not found, the value of the textbox2 is "Not Found" or something...
i have 2 userforms one with textboxes and the other one with listbox and textboxes.
Everytime user input their data(ie:first name, last name, address etc) in the first form the data's going to be saved in Worksheet("customerSheet") and later on to be displayed in the second form. using the listbox you can select the customer's name and the customer info will be displayed in the textboxes.
here's my code in second form
Private Sub UserForm_Initialize()
Worksheets("CustomerSheet").Activate
Range("A1").Select
'ActiveCell.CurrentRegion.Name = "Database"
'Selection.End(xlDown).Select
'ActiveCell.address(False, False)
viewCustomerBox.RowSource = "A2:A15"
End Sub
I have one table with 0.5" cell widths. Good.
I have another table with 1.875" cell widths. Good.
I want both tables on the same page, one under the other. Hard.
How can I achieve this? Is there a way to have the column widths of one table independent of the other on the same page? Is there a workaround that would achieve the same effect?
I am doing a if else statement but I do not whats the keyword for getting the column width value
Eg
if column width value = xx
column width value = xxo
I have made a spread sheet where the row 1 - 20 are 12 wide but I'd like rows 20-25 to be a lot wider. I've looked around and can't find the solution since I don't know how to explain it in a few short words.
View 1 Replies View RelatedThis is doing ALMOST everything I need it to do. The only thing I need is for it to keep the formatting and column widths from the source sheet.
[Code].....
I can automate chart creation in VBA, but is there any way to also have it such that the x-axis of the chart begins with the minimum x-value and ends with the maximum x-value in the data set?
View 2 Replies View RelatedI need a formula to calculate the sum of a range of rows.
Example:
If rows 1-10 have a width of 12.75 each, and rows 11-15 have a width of 15.5 each. I need a formula to tell me that the sum of the widths of rows 1 to 15 is 205.
Any idea?
how can know how is my column width in CM ,
View 9 Replies View RelatedI have a 3 column listbox on a userform. I am trying to line the columns up with some label headers on the user form.
On thsi listbox, how can I code the first column to a width of 250, the second column to a width of 50 and the third column to a width of 75?
I would like to know if it's possible to change width of different cells in a single column? Example:
Width of cell A1 would be 10, and width of cell A2 would be 15. Can it be done? If yes how, if no damn. The reason I ask is because I trying to fit a report on one page with a chart and a month worth of data, but some cells are to long and they get cut off.
I've used the macro recorder to try to auto-apply column widths after I do a CSV import:
(A&B=3.14, C=8, D=13.57, E=24.14, F=9, G=10, H=11.29, I=8.57, J=6.86, K=8, L=13, M=10)
...but for some reason when I execute this macro, "every" column gets the width of 10!
how I can fix the below code?
Sub SetColumnWidth()
Columns("A:B").Select
Selection.ColumnWidth = 3.14
Columns("C:C").Select
Selection.ColumnWidth = 8
Columns("D:D").Select
Selection.ColumnWidth = 13.57
Columns("E:E").Select
Selection.ColumnWidth = 24.14
I have lots of files I need to read every day. I need to read them in as fixed width - EX: column A is 1-8, column B is 9-12, column C is 13-22, etc. Is there a way to create a VBA macro or something so that I don't have to point and click to draw lines every day when I read in these files?
View 9 Replies View RelatedI am trying to create a userform that is reusable by turning on and off diff objects, and reusing objects. I am running into a little difficulty of resizing the list box for various lengths if items Example would be if I have a list of items and the longest one is 93 chars long, I need a width of 672 When I have a list that the items are each 5 chars long, I need a listbox width of 92
For simplicity's sake, I am using Courier (supposed to be a monospaced font) pitch is 10. I would have figured that my width would be simply a matter of finding the longest length in the list and then multiply that times some magical number that represents the width of the letter (since monospaced, each letter should be the same).
But, with the 93 char long string, the "average length" of each letter seems to be 7.2 (672/93 = ~7.2) but for the shorter words, it seems to be wider at 18 (92/5 = ~18)
Is there a way to have the listbox size itself to the width of the strings?
Is there any way to measure the width of text in a cell in points? I know that the width of the cell is returned using the Width property.
I also know that the number of characters in the text can be returned using the LEN() function, but I don't believe the number of characters can be converted to points (because some characters are different widths, if I'm not mistaken)
if it is possible to change the border thickness weight via points? Eg., "1/4 pt".
I find that the xlthin border prints too thick. The only way I've been able to reduce it to appearing thin when it prints is by choosing the second option under "None" in the Line Style option of the Format Cells dialog box.
While that approach prints a thin border, it is actually a dotted line and looks a bit odd on screen.
Suppose I have the following UDF, which returns the width of the column, but when the column width change (increase or decrease), the UDF is not able to recalculate the column width...what should I do? .....
View 9 Replies View RelatedIs it possible to independantly change the width of donut chart series? I want the outer ring of the donut to be slimmer than the inner ring.
View 4 Replies View Related
