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")
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 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:
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?
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") 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:
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:
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.
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?
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.
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 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.
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.
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.
Dim sinlen As String Dim sinformat As Variant Dim lastcell As Variant
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?