Free Microsoft Excel 2013 Quick Reference

Setting cell size to scale

I need to structure a worksheet with cell height 2.5 times the cell width. I
need the cell to represent a plot 4 ft wide by 10 ft height. Is there a way
to format a worksheet in this way that does not involve repeated printing and
measuring with a rule?!


Post your answer or comment

comments powered by Disqus
I need to structure a worksheet with cell height 2.5 times the cell width. I
need the cell to represent a plot 4 ft wide by 10 ft height. Is there a way
to format a worksheet in this way that does not involve repeated printing and
measuring with a rule?!

I'm currently working with a lot of comma separated value (.CSV) files. Whenever these files are opened in Excel (2000), since all size information is not present, each cell is given the same tiny size.

What I need is either
1. An option that says "automatically fit cell sizes to contents upon file opening", or

2. A method of fitting cell sizes to contents that is quicker and less tedious than double-clicking at the top of each column (since there are 40 or so columns in each spreadsheet).

Before you ask, no I can't just save the files in .XLS format, they need to stay as .CSV files for compatibility with other software.

Do either of my options exist?

Thanks in advance for your response,
Richie.

I am creating an Excel workbook using VB.NET, and have run into a
problem. Excel at times insists on reformatting data that I enter
into cells, e.g., converting "01234" to "1234", and this screws me
up when I need to read the data back. When I run into this problem
using Excel interactively I simply change the cell Number format
from "General" to "Text", but I haven't been able to figure out
how to do this using VB.NET. Here is a code sample:

Dim wb as Microsoft.Office.Interop.Excel.Workbook

[...workbook is created...]

Dim style as Microsoft.Office.Interop.Excel.Style

style = wb.Styles.Add("Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPattern Solid
style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLe ft

I use more than one style, and once I've created a style with the
features I want I apply it to the ranges where I am entering data.
Styles also have a "NumberFormat" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFormat = "Text"

all that happens is that I end up with a weird custom format.
Nothing else I try seems to work either. Can anyone tell me how
to do what I am trying to do?
--
John Brock

I am creating an Excel workbook using VB.NET, and have run into a
problem. Excel at times insists on reformatting data that I enter
into cells, e.g., converting "01234" to "1234", and this screws me
up when I need to read the data back. When I run into this problem
using Excel interactively I simply change the cell Number format
from "General" to "Text", but I haven't been able to figure out
how to do this using VB.NET. Here is a code sample:

Dim wb as Microsoft.Office.Interop.Excel.Workbook

[...workbook is created...]

Dim style as Microsoft.Office.Interop.Excel.Style

style = wb.Styles.Add("Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLeft

I use more than one style, and once I've created a style with the
features I want I apply it to the ranges where I am entering data.
Styles also have a "NumberFormat" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFormat = "Text"

all that happens is that I end up with a weird custom format.
Nothing else I try seems to work either. Can anyone tell me how
to do what I am trying to do?
--
John Brock
jbrock@panix.com

Hello. I am using VBA to create a new worksheet when it is run. However, when I use VBA to insert values into a particular cell, it uses the "General" formatting for the cells. In an instance of inserting "060706" into the cell, it chops the opening 0 off, and I need this 0 later on in my code. I know that changing the cell properties to "Text" will eliminate this problem. How do I do that using VBA?

Here is what I have now:


	VB:
	
 ExcelObj = CreateObject("Excel.Application") 
ExcelObj.Visible = True 
ExcelObj.Workbooks.Add 
 
Range("M1").Value = EmpNum 
Range("M2").Value = IMSP 
Range("M3").Value = SID 
Range("M4").Value = BDay 
Range("M5").Value = SSNo 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks,
tadoogie

Basically as the title implies, is it possible?

I know you can set cells equal to other cells, but can you set the cell equal to a combobox created in VBA.

If so, how might one accomplish this task?

I am trying to get a cell in a column to be equal to another cell in a different column. But i do not want to actual value of the cell because if the cell changes i want the one with the formula to change also.

this code is giving me close to what i want, but it's giving me =8.6667
when i need the cell to be saying in the spreadsheet =AQ9

if you don't understand what i'm trying to ask then say so because I know it sounds kinda funky. thanks for your help.


	VB:
	
 
With rng 
    rng.Activate 
    ActiveCell.Offset(1, 0).Select 'selcting first cell to get value
     
    ActiveCell.FormulaR1C1 = "=" & Range("AQ9") 
     
     
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have code that will set the size to 85%. But I was wondering if there's a way for me to set the size if row 33 has text on page 2?

Code:
'Page Setup to 85%
    With ActiveSheet.PageSetup
        .Zoom = 85
        .Orientation = xlLandscape
    End With
I'm trying to do my own version of the Fit 1 page wide by 1 page tall.

Thanks!

Hello,

I am pasting a large amount of HTML into cells, and would like the cell size to stay at the default, so that the screen is managable. Is there a setting to accept the pasted contents, and abbreviate what is shown, and not resize cell?

Thanks, this forum is always a huge help!

Does anyone know if there is a way to limit a cell/column to a certain number
of letters and/or numbers. I want the system to stop accepting data once I
hit 25 characters. This would save me having to count the data to make sure I
haven't exceeded 25.

Thanks

I would like to create a daily timeline to record what activity is done and how long it takes. The cell will have a color background for each type of activity. The timeline would run from 6 am to the following morning at 6 am.

Can I do this be scaleing the width of a cell. I would like the time to run side by side for a 24 hour time line.

I have a cell that is auto populated from another worksheet. It is a cell
that contains what can be quite a long paragraph of text which can contain
multiple lines that have been input using (ALT - return).

The cell has been set as a merged cell of 10 rows (to allow for space if the
source data is across multiple lines).
I want this cell to be able to cope with whatever size of data the source
data contains). At the moment I have just left 10 lines available, but if
the source data cell contains information that is over 10 lines then this
will be cut off from the destination cell.

I want a way of handling this so in the cases where there is more then 10
lines, the destination cell pushes down all the rows below it to accommodate
for the extra space requirements.

In an ideal world, I could just use both shrink to fit and Wrap text,
because then if it is over 10 lines, the fonts just reduce in size to allow
for more, but if I turn this on, I have to turn off wrap text, which is
necessary because the contents is in a mutli line cell.

I do hope this makes sense and I look forward to any suggestions how one can
deal with this.

Kind regards

Dave

I have a form with cells used for text entries. How can I automatically increase the size of the cell with text too long to show? I want the cell to increase in size vertically one "line" (or however many lines necessary). Thanks.

I have a sheet with a button that enables the user to put a comment in a cell. The code is set up as per what I found here for entering a comment on a protected sheet. The problem no is that the font size is wat too small in the comment box. I want to set the size to 12 pt. How can I change the default font size for coments or at least set it when I put a comment in from VB?

How do I determine what cells a drawing object is sitting over? Alternately, can I set the PrintArea in drawing units?

I am drawing a few graphic objects onto a page (text boxes, lines), and I want to print the objects. Hence, I want to set the PrintArea to the exremities of the drawing objects I have placed.

Thanks for your help.
Cheers,
Scotty.

This should be easy but...

How can I set the value of cells in VBA? I've tried various combinations of Cells(a, b).value = ..., Set Cell(a, b) = ... and so forth but nothing works. I can't even set a single cell value successfully.

The complicating factor is that I don't have a user-defined range for the output. My function/sub takes a rectangular range of any size as input, then calculates a matrix of values (very much like Excel's Correlation Coefficient matrix) - it's tricky for the user to know what size to make the output range.

What I'm HOPING to do is have the user enter the input range and arguments as a FUNCTION in a single cell (in an empty area of a worksheet) and have VBA create the output matrix with the ActiveCell (where the function was entered) as the top left cell of the matrix.

I've tried using ActiveCell.Offset(a, b) = ..., Cells(a, b) = ..., etc. but nothing works (partially because I think Cells() has to be part of a range). Can I set the value of cells without a defined output range? If not, how can I create an output range? Do I have to create an array function?

Thank you.

I have a spreadsheet with 4 columns, 100 rows. At times, the content in Column B, for a specific row, needs to be printed to a label printer. The user needs to be able to 'point-click-print' on a row in the spreadsheet to initiate printing.

My solution has an Option Button (radio button) adjacent to each row. When a button is selected, the user is prompted to enter the number of printouts he would like. When he hits OK, only the content of (Column B) of the respective row must print, and it should print as many times as the user requested in the prompt box.

I welcome ideas on how to better address this need.

Questions:

1. How do I pass the range of a cell as a variable from the OptionButton click event to the PrintLabel module? (For example, when the user clicks on the OptionButton for Row 9, I want the corresponding content in cell B9 to be printed). I'd like to only use one instance of the PrintLabel module and call on it with different values (note, the code below is hard coded with B9, which is what I'd like to change to be more dynamic)

2. Despite capturing the user's desired number of copies, only one printout is generated. Any ideas on what I'm doing wrong?

3. If you enter a non-numeric in the field for number of printouts, it notifies you that a numeric is required. After entering a numeric, hitting OK or Cancel generates the VB error "Run-time Error 1004 PrintOut method of Range class failed" Any ideas why?

4. How would I be able to cap the number of printouts allowed to be entered in the prompt box to no more than 25?

5. How can I adjust the test size of the printout?

6. How can I have the printout contain the only the cell content, and not part of the header that is configured to print when the entire page is printed? (see attached .pdf - all that's needed is the bottom text)

Thanks in advance!


	VB:
	
 
[Sheet 3 (Data Entry)] 
 
Private Sub OptionButton1_Click() 
     
    PrintLabel 
     'Call PrintLabel module
     
End Sub 
 
 
 
 
[Module 6] 
 
Sub PrintLabel() 
     
     ' Ask user to input the number of copies to be printed.
     
    Dim rngToPrint As Range 
    Dim Message, Title, Default, MyValue 
    Message = "Please enter the number of inventory labels to print" 
     ' Set prompt.
    Title = "Number of inventory labels" 
     ' Set title.
    Default = "1" 
     ' Set default.
     
     ' Display message, title, and default value.
    MyValue = vbNullString 
    MyValue = InputBox(Message, Title, Default) 
     
    If MyValue = vbNullString Then Exit Sub 
    If Not IsNumeric(MyValue) Then 
        MsgBox "Numeric entry only please" 
        Run "PrintLabel" 
    End If 
     
     
    Set rngToPrint = Sheets("Data Entry").Range("B9") 
    rngToPrint.PrintOut Copies:=MyValue, Collate:=True 
     'print range and number of copies requested
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


ok 2 questions actually... I'm new to excel without any training in it whatsoever and I'm wondering... 1.) how do a set a cell to be = to the value selected from a drop down menu... and have the cell display the exact value, ie. "Bob" "Dave" or "Suzy" rather than 1, 2, or 3??? 2.) How can I set excel so that if I Insert a row in somewhere it won't throw off any formulas I already have programmed in below the inserted line? I haven't looked into this one yet, i dunno if it's a valid problem... some guy in my office mentioned he was having trouble with it though... and I'd like to give him a hand... Thanks a ton to anyone who can help me out here!

I have many sheets with cell pattern set different colors using VBA conditional format. Many cells will have no results or zeros and to reduce confusion I would like the text to be the same color as the cell pattern color. What I don't know is how to test the cell for its pattern color so I can then set the text to the same color.

Thanks,
BC

I've got an enfuriating problem using the Solver in VBA.

I use a range to hold my "SetCell" by "Varying" values. I then loop over the range rows, resetting the solver each time and setting the cell references to vary etc. then adding a constraint. Finally I run the Solver. Trouble is the cell references are not being set as I loop over the range...


	VB:
	
 cmdRun_Click() 
     
    Dim rng As Range 
    Dim Row As Integer 
    Dim ref1 As String, ref2 As String 
     
    Set rng = Range("M7:M32") 
     
    rng.Value = 2 
     
    For Row = 1 To rng.Rows.Count 
         
         'Set value refrence...
        ref1 = rng(Row, 1).Offset(0, 2).Address(False, False, xlA1) 
         'Vary reference...
        ref2 = rng(Row, 1).Address(False, False, xlA1) 
         
         '========================================================
         
         'Reset the solver...
        Call SolverReset 
         
         'Solver Options...
        Call SolverOptions(MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _ 
        :=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _ 
        IntTolerance:=5, Scaling:=True, Convergence:=0.0001, AssumeNonNeg:=False) 
         
         'Cells to vary...
        Call SolverOk(SetCell:=Range(ref1), MaxMinVal:=2, ValueOf:="0", ByChange:=Range(ref2)) 
         
         'Add a constraint...
        Call SolverAdd(CellRef:=Range(ref2), Relation:=3, FormulaText:="0.0001") 
         
         'Solve...
        Call SolverSolve(True) 
         
         '=========================================================
    Next Row 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can anyone see what I'm doing wrong?

I have a function that spits out a 1 dimensional array "output(1 to 47743)"

I tried to use this function in my sub and set it equals to a dynamic array, how come it doesn't work?? the coding go though without crashing, but when I try to retrieve the data, it failed.

Here is the coding:


	VB:
	
 
Sub run() 
     
    Dim a, b, c As Long 
    Dim TotNum As Long 
    Dim Oopen 
    Dim Sma() As Variant 
    Dim MA(1 To 3) As Variant 
     
    a = 3 
    Do While (Worksheets("Sheet1").Cells(a, 2)  0) 
        a = a + 1 
    Loop 
    TotNum = a - 3 
     
    Redim Oopen(1 To TotNum) As Double 
     
    For a = 1 To TotNum 
        Oopen(a) = Worksheets("Sheet1").Cells(a + 2, 2) 
    Next a 
     
    Redim Sma(1 To TotNum) 
    Sma = MovingAve(Oopen, 3) 
     
    Worksheets("Sheet1").Cells(4, 4) = Sma(9)   1 Then 
        b = 1 
        For c = N + 1 To size 
            sum = sum - data(b) + data(a) 
            Output(c) = sum / N 
            a = a + 1 
            b = b + 1 
        Next c 
    End If 
     
    MovingAve = Worksheets.Application.Transpose(Output) 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I want to restrict a cell to only allow numeric values that must be 6 characters in length. I set the Data Validation to Text Length of 6, however am unable to figure out how to restrict the cell to numeric values only.

Thanks
HTH

I'm trying to find a formula which allows me to tell which cells in a large set add up to a set amount.

For example: Let's assume these are the cells I have on my spreadsheet.

1
3
6
12
13
16
25

I want to create a formula which will allow me to tell which combination of cells listed above add up to: 25

I need it to pull (3,6,16) (12,13) (25) and so on...

Any help would be greatly appreciated!

I know this should be very simple, but for some reason nothing I am doing works

I have two worksheets open, how can I set a cell in one worksheet equal to a cell in the other wowrkseet using VB?

any help appreciated!
thanks!


No luck finding an answer? You could always try Google.