I have a textbox that displays text from another cell in the workbook. The other text has some sections in bold, but when it displays in the textbox it doesn't show the bold text - The code I'm using at the moment is below.
Private Sub UserForm_Initialize()
Dim R As Long
ComboBox2.List = Array("QuestionSet1", "QuestionSet2")
[Code].....
I'm concatenating data in 5 cells (Author, Year, Title, City, Publisher), one of which is in italics (Title). When using concatenate (or &) the formatting is removed, and I need to be able to retain that formatting.
Example:
Col B
Aaron, M. (ed)
1999
The Body's Perilous Pleasures.
Endinburgh
Edinburgh University Press
I am attempting to format some TextBoxes from within a For/Next loop. I need a way to check which TextBox is the active TextBox in the loop. Using i as the variable, I came up with this code snippet: Me.Controls("TB" & i).Text = Format("TB" & i, "mm/dd/yy")
If i = 3, this gives me in TextBox3 (which is called TB3) the text 'TB3' and not the value of what is in TB3. It has got to bo something simple, I just can't see it!!!
My data has various dates in column A, which I group into months using the formula "=DATE(YEAR(A2),MONTH(A2),1)" in column B. Then I format column B to show the results in the format mmm-yy. So far so good.
I then use a pivot table to count the number of entries for each month and I have formatted the date column the same way. But every time I refresh the data (which I need to do frequently as data floods in from external sources), the format of the pivot table reverts to dd/mm/yyyy and I have to go and manually reformat it again.
I've checked all the options I can think of, but cannot find any way to keep the format as I have set it. Does anybody know of a way?
I'm using Excel 2003 on Windows 2000 Professional.
I'm using the following code to insert rows in a table of data:
Code:
For i = LR To 2 Step -1
If Range("D" & i).Value Range("D" & i - 1).Value Then Range("A" & i).EntireRow.Insert
Next i
It works as expected, but as my data table is formatted, the last row inserted does not contain the same formatting as the table.
I use pivot tables extensively in my job. I have several spreadsheets that feed from acess database tables to build pivot tables. Besides the fields imported from Acess, I also create calculated fields. The problem is, for both the calculated fields and the imported fields, the number formatting does not stick. I know how to go to field settings, then to number, and change the customized format. The problem is, once I take that field out of the pivot table and bring another one with a different format (say, bring a field with currency format and take away one field with a percentage format) and then bring the original field back to the privot table, the number format is gone.
This is really annoying since I work with dozens of fields, and it takes time to change the number settings every time I change fields on the pivot table, and the number of decimal places, etc. I stress that I know how to change the number formatting on fields, it's just that it excel does not remember it afterwards.
I thought that maybe the formulas in excel might allow one to include the formating, but I have not been able to find if this is possible, and if so, how to do it. So, for example, the following formula for a calculated field:
=IF(ISERROR(Contribution/'Block Hours'), "0", (Contribution/'Block Hours'))
would be nice if the formula itself could include the number formating. I tried including something like , 0.00% at the end, but it does not work.
I have a number in A1, another number in B1, a Date in C1. In D1, I am referencing all 3 of the other cells: =CellA1 & "-" & CellB1 & "----" & CellC1. Trouble is the date is coming through as a number. Is there a way that I can get the date to show as displayed as opposed to the value of the cell?
View 2 Replies View RelatedI have written some code which takes data from multiple spreasheets and formats it in a specif way. However one problem I have is getting the right data format. As you will see from the input files the date and time cells are separated into two and the macro is supposed to aggregate these together in one cell with a space in between - but I cannot manage to perfect this !!! The output is in a number format instead. I have attached the input files. The code written so far is as follows - works perfectly apart from date format that comes is in the output file once you run the code.
Dim Obj1 As Object
Dim Obj2 As Object
Dim UserList()
Sub RunMe()
Dim Str1 As String
Dim myPath As String
Dim i As Integer
Set Obj1 = CreateObject("excel.application")
myPath = "C:Test" 'please note you should a "" at the end.
Str1 = Dir(myPath & "*.xls")
Set Obj2 = CreateObject("excel.application")
Do
Obj1.Application.DisplayAlerts = False
Obj1.Workbooks.Open (myPath & Str1)
'For i = 1 To UBound(userslist).............
I am importing a data table from Excel into word. All works well except for the first column of my data, which contains numbers formatted as currency. I am using a bunch of loops to transfer the data into word, and for each cell the code looks like this:
[Code]....
Is there a way to tweak the code and preserve the formatting when I am importing numbers into word?
I tried the following code but it gives me type mismatch error:
[Code] ....
I have 2 sheets in one workbook; one sheet has a chart with a data table, the other contains the source data for the chart. I want to copy and move the sheet with the chart to a new workbook, but without the source data. I break all links, but then the chart's data table takes the default values and formats in the new workbook after I break the links. For examples, the source data has formats such as currency and no decimal places. However, after copying the sheet, the data table reverts to no dollar sign and decimal places. In addition, the months on the x-axis turn to numbers when the links are broken.
View 3 Replies View RelatedI am using excel 2007 - I need the extra available columns in Excel 2007 for my application.
I have a spreadsheet that has a significant amount of conditional formatting. I want to copy a range from this spreadsheet to another spreadsheet. In the new spreadsheet I want to remove the conditional formatting rules whilst retaining the actual formats those rules generated.
Format painter and "paste special - format" copies any formats as well as the conditional format rules.
Is htere a method within Excel to do this or can someone suggest a workaround?
I've been using the following code to conditionally format userform textboxes based on a specific value (in this case 2490):
[Code] ........
What I'm looking to do now is amend this so rather than use a specific value, to use the value in a specific textbox on the same userform.
Could anyone offer advice on how I would go about accomplishing this task? I'm trying to format two textbox fields on my userform that when the user inputs data into it, it would automatically show the "symbols, dashes and/or spaces accordingling".
The first textbox field (TextBox9) I need to end up looking like this, with the numbers and/or letters changing by user input.
example: (MI) A000-000-000-000
The next textbox Field (TextBox10) I need to end up with this type of result:
example: (MI ID) A000-000-000-0
I was trying something like this but I'm not familiar enough with the formatting symbols to accomplish this task.
I'm a bit stuck with one of my userforms. I have a username textbox on my workbook logon screen. I have put a max length on the textbox to 7 characters. I have also added a bit of code which stops the user from entering anything less than 7 characters (once the commandbutton is clicked, a msgbox comes up), and the textbox will show text in upper case.
What I would like is a way to "format" the textbox. All of the usernames will be in the following format: BBAAA11. The first 2 characters will always be "BB". The 3rd, 4th and 5th characters will be any combination of letters (for example... "CAM", "EAL", "BEF"). Finally the last 2 characters will always be any combination of 2 digits (for example... "76" "24", "00").
If someone could provide me with some code which will not allow the textbox to type anything other than the specified format, or for a msgbox to appear once the commandbutton has been clicked to say that the incorrect format has been entered, I will be most grateful.
I have a timesheet, with a custom format of [h].mm. In my userform, I have a textbox which I would like to use to enter a time in, but when I run the macro, the time shows up correctly (such as 0.12 for 12 minutes), but it is still calculating as text. I've tried all the various codes I have found throughout the board but nothing has worked so far. I can get as far as making 0.12 minutes show as 12.12.00 AM, but then it shows the whole time instead of the 0.12 which the cell is formatted to do and does not calculate it at the bottom where I grab all the times from that column.
View 1 Replies View RelatedA text box on a Userform inputs numbers to a cell in a worksheet. I want the number to appear in the text box formatted #,##0.00 However, if I include the line
Code:
csDepositTextBox.Value = Format(csDepositTextBox, "#,##0.00")
in either the csDepositTextBox_Change or _AfterUpdate events, it causes the number to be stored as text in the worksheet. Curiously I can put the code in the corresponding event for another textbox and it does not corrupt the formatting.
I need to have textBox2. formatted to a number with 3 decimal places.
The code below is executed from the command button.
Code:
Private Sub CommandButton1_Click()
If IsNumeric(Me.TextBox1.Value) Then
Me.TextBox2.Value = Me.TextBox1.Value / 25.4
End If
End Sub
I found the code below but I cannot seem to figure it out
Code:
Private Sub TextBox2_Change()
TextBox2.Text = Format(Number, "0.000")
End Sub
Is it possible to protect an inserted text box from being moved, stretched, etc. while allowing data input into the cell/s? I'm using a 2003 version of excel.
I've attached the sheet in question. The yellow text boxes are what I want to lock, yet still allow data input.
excel tip example.xls
I have several text boxes on a 'picture' which is the format for a business review. The text boxes are linked to cells behind the picture which picks up company names, cities etc...
Two of the boxes have a phone# and date
The linked cells are formatted correctly but obviously the text boxes, pick up the 'values' not the formats.
is there any way for the textboxes to show the values with the proper format i.e.,
xxx-xxx-xxxx
mmm yyyy
I am building a large Userform and I am going back to edit some of the controls (renaming, resizing, etc). The problem is, after editing, the changes don't stick. I can open another module and then open the form again and the changes will be gone. I can save Excel, close the file, and open it again, and the changes will be gone.
View 4 Replies View RelatedIs it possible to retain the formula in particular cells within an excel spreadsheet whilst clearing the rest of the sheet, and if so how?
View 3 Replies View RelatedI have a list that looks like this:
1. Here I have som formatted text.
2. Here too.
10. And here.
110. As well as here.
The content of each line is in a single cell.
How do I go about automatically chopping off the number, the dot and the space before the text, but retaining all formattings?
Seems like a dumb question but is it possible to unmerge say one "3 cell" merged cell with a value of "A" into 3 SINGLE CELLS with a value of "A"?
I need a solution so that each day I can get a value from a single cell but it appears in a new column each day.
eg, A1 contains a numerical value (based on a formula)
Each day there is a new column, say, Monday = B1, Tuesday = C1 etc.
On Monday B1 takes the value of A1 and 'freezes' the value in B1
On Tuesday C1 takes the value of A1 and 'freezes' the value in C1, but the value in B1 remains as it was when it was originally frozen.
I have a quantity column on an inventory sheet which automatically updates the available quantity based on sales from a sales sheet. But I need to know the date the quantity reached zero.
That date would always be "today," on the day the quantity = 0, but how do I make the date NOT update to "todays date" on the next day, and the next, etc. It needs to stay as the date that 0 qty was reached.
i am using the following code to put data into a worksheet which it does perfectly. once the data is put into the sheet it is saved. what i want to do next is to retain the data in the userform and possibly change one or two entries and put data back into the sheet to save again
Option Explicit
Dim ans As Variant
Dim MyArray(100, 4)
Public MyData As Range, c, d As Range
Private Sub cmbAmend_Click()
Application. ScreenUpdating = False
Set c = Range("b4").End(xlUp).Offset(3, 0) ' c selected by Find
c.Value = Me.DTPicker1.Value ' write amendments to database
c.Offset(1, 0).Value = Me.TextBox1.Value
c.Offset(2, 0).Value = Me.TextBox2.Value
c.Offset(3, 0).Value = Me.TextBox3.Value
c.Offset(12, 0).Value = Me.ComboBox1.Value
Set d = Range("b16").End(xlUp).Offset(1, 0)
how do you store formulas so that they stay in the cells when all info is deleted? so i can use the same spreadsheet every week but all i have to do is select all and delete. if i do it now i lose al my formuals.
View 3 Replies View RelatedI can't seem to get the Concatenate function to work in my macro when there is a combination of numbers and text. It works fine is there are no alpha characters. I am using this macro to format all of the data in column "A" to have 5 characters and be text. These values will later be used in formulas and Pivot Tables.
Sub Macro2()
Dim sinlen As String
Dim sinformat As Variant
Dim lastcell As Variant
Range("b1").Select
Selection.EntireColumn.Select
Selection.Insert Shift:=xlLeft
Range("a1").Select
Do
ActiveCell.Value = "'" & ActiveCell.Value ......
I am programming some kind of database in excel and using comboboxes as drop down lists to enter some of the information in the DB. My problem is that when the file is saved, closed and then opened again, all of the comboboxes have lost their selection. I think that using the ControlSource might help but for some reason I can't find the proper syntaxe to make it works. Actually I am getting a Run time error 438: This object doesn't support this property or method.
I have included the relevant portion of my code below. Is the ControlSource property going to solve my problem? If so, what is the syntax I must use? If not, what can I do to make sure that my ComboBoxes are going to keep their selection?
ActiveSheet. OLEObjects.Add(ClassType:="Forms. ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=hori_offset - 220, Top:=vert_offset + 78, _
Width:=180, Height:=24.75).Select
With ActiveSheet.OLEObjects("ComboBox1").Object
.Font.Size = 14
.Font.Bold = True
.Style = fmStyleDropDownList 'Use drop-down list
.BoundColumn = 0 'Combo box values are ListIndex values
End With
With ActiveSheet.Shapes("ComboBox1")
.OLEFormat.Object.ControlSource = "Q1"
.OLEFormat.Object.ListFillRange = "M1:M8"
End With
I need to create a variable that will be TRUE/FALSE based upon whether the user has made changes to a certain range without running a specific macro. Basically, if they change cells in a certain range, the 'Update' macro must be run. I want this variable to be False if the cells have changed without running 'Update' and then when 'Update' runs, it sets the variable back to True.
I've been able to make this work with a Public variable, but if the user closes the workbook, the variable resets to False. Is there a way to create a variable that will be remember even if the workbook closes?