Retain Formatting In Textbox?

Mar 1, 2013

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")


View 3 Replies


Retain Formatting During Concatenation

Feb 4, 2009

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.

Col B
Aaron, M. (ed)
The Body's Perilous Pleasures.
Edinburgh University Press

View 4 Replies View Related

Formatting TextBox And Check Which TextBox Is The Active TextBox In The Loop

May 18, 2006

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!!!

View 2 Replies View Related

How Can I RETAIN Formatting In A Pivot Table

Mar 21, 2007

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.

View 9 Replies View Related

Retain Formatting When Inserting Rows Via VBA?

May 21, 2014

I'm using the following code to insert rows in a table of data:

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.

View 1 Replies View Related

Retain Calculated Field Number Formatting

Aug 16, 2006

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.

View 4 Replies View Related

Join Cells & Retain Date Formatting

Nov 30, 2007

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 Related

Join Time & Date & Retain Formatting

Jan 29, 2008

I 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")
Obj1.Application.DisplayAlerts = False
Obj1.Workbooks.Open (myPath & Str1)
'For i = 1 To UBound(userslist).............

View 6 Replies View Related

Paste Number From EXCEL To WORD And Retain Formatting

Aug 6, 2014

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:


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] ....

View 1 Replies View Related

Copy Chart Without Source Data & Retain Formatting

Feb 21, 2008

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 Related

Remove Conditional Formatting Conditions, But Retain Formats

Jul 2, 2008

I 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?

View 3 Replies View Related

Conditional Formatting Userform Textbox Based On Textbox Value?

Jul 3, 2014

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.

View 3 Replies View Related

Userform Textbox Formatting

Feb 28, 2009

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.

View 14 Replies View Related

Userform Textbox Formatting ..

Jan 31, 2010

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.

View 7 Replies View Related

Formatting Userform Textbox To Time?

Jul 22, 2014

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 Related

Formatting Numbers In Textbox On Change Or After Update

Jun 29, 2012

A 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

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.

View 3 Replies View Related

VBA / Formatting Textbox To Number With 3 Decimal Places

Nov 21, 2012

I need to have textBox2. formatted to a number with 3 decimal places.

The code below is executed from the command button.

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

Private Sub TextBox2_Change()
TextBox2.Text = Format(Number, "0.000")
End Sub

View 2 Replies View Related

Excel 2003 :: Protect Textbox From Formatting But Not Input?

Mar 15, 2013

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‎

View 2 Replies View Related

Formatting ActiveX Textbox For Phone Numbers And Dates

Aug 28, 2012

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.,

mmm yyyy

View 2 Replies View Related

Userform Will Not Retain Changes

Sep 20, 2006

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 Related

Retain Formula In Cell

Feb 4, 2009

Is 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 Related

Retain Text Formate

Jun 11, 2009

I 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?

View 9 Replies View Related

Unmerge But Retain Value In All Cells

Aug 10, 2009

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"?

View 9 Replies View Related

Retain Data From A Formula

Oct 30, 2009

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.

View 9 Replies View Related

Set And Retain The Date That A Value Is Reached

Dec 10, 2009

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.

View 9 Replies View Related

Retain Data On Userform

Jun 30, 2006

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)

View 6 Replies View Related

Retain Formulas When Deleting

Dec 17, 2006

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 Related

Retain Leading Zeros

Dec 29, 2006

I 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

Selection.Insert Shift:=xlLeft


ActiveCell.Value = "'" & ActiveCell.Value ......

View 9 Replies View Related

Retain Combobox Selection

May 30, 2007

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

View 4 Replies View Related

Variables That Will Retain Value After Workbook Closes?

Aug 5, 2014

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?

View 2 Replies View Related

Copyrights 2005-15, All rights reserved