VBA Macro To Create & Format Text Box On Worksheet
I have code that creates a textbox on a worksheet, copies the contents of a cell from another worksheet to that textbox. I want to be able to select that text and format its appearence ( center the text, bold and font style and size). This is what I have so far but I do not know how to select the text that was imported.
Sub textmove()
Dim bname As String
Sheets("cover").Shapes.AddTextbox(msoTextOrientationHorizontal, 96.75, 512.25, _
230.25, 120#).Name = "client"
bname = Sheets("data").Range("a3").Value
Sheets("cover").Shapes("client").TextFrame.Characters.Text = bname
With Selection.Characters(Start:=1, Length:=17).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End Sub
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
VBA Macro To Create New Worksheet, Charts, Etc.
I need a macro to create the following worksheets and charts from an Excel data set: Three (3) worksheets (already created manually in attached Excel file): 1. Chart Data. (a) Column A in Chart Data is always numbered 1 - 600 (50 years x 12 mos/yr). (b) Column I and column Y data sets (from Prod_Month) created in Chart Data. Each data set can be identified and collated with column F in Prod_Month (API) which is unique for each dataset. 2. Rate vs. Month - plot of Daily Gas (col. Y in Prod_Month) vs. Months (col. A in Chart Data). 3. Rate vs. Time - plot of Daily Gas (col. Y in Prod_Month) vs. Calender Time (col. B et. al. in Chart Data) At a minimum, could someone help me create the Chart Data worksheet from the data in PROD_MONTH? Charting all the columns takes time from Chart Data but any data manipulation macro(s) help.
View Replies!
View Related
VBA Loop Through Worksheet And Format Each Column As Text, Or Date Based On Header
I am trying to figure out a way to condense my code (improve my code) Right now I am using a Macro in Excel to go through a workbook by selecting each column individually and if there is something in the header then formatting that column as text. The problem with the way I am currently doing it, is that due to size limitations on the VBA code, I can only select columns A to Z, and if there is more data in the worksheet it is not formatted. Also I would like the code to check the header and if the word date is in there, format it as a date instead. Also if there is a way to delete any invalid ranges in the workbook Sub Format() Dim VarFileName As String Dim VarPath As String Dim VarSavein As String Dim wsheet As Worksheet VarSavein = Sheets("sheet1").Range("C2").Value VarFileName = Sheets("sheet1").Range("A2").Value VarPath = Sheets("sheet1").Range("B2").Value Workbooks.Open VarPath & VarFileName For Each wsheet In ActiveWorkbook.Worksheets Sheets(wsheet.Name).Select Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ := Array(1, 2), TrailingMinusNumbers:=True..........................................
View Replies!
View Related
VBA Macro To Multiple Text Files Based On Headings On Worksheet
I have created an Profit&Loss excel sheet with all the accounts in column A and then the values for each month, January to December in column B to column K like this: Column A...............Col B............Col C........Col D........Col E.....and so on Account 1..............2222............58452........5255.........5844.... Account2................5.................45...............25...........458 .... I've made a macro that creates a textfile and saves it on my desktop, but I get a text file with all the info in the same file, what I want is to get one text file for every month, that is text file number one(january) is: The heading info+column A+columnB text file number two(February) is: The heading info+column A+columnC text file number three(March) is: The heading info+column A+columnD and so on In the heading info I have to state the period and then I need the macro to change this for every text file, that is for the january file it has to be "1 to 1", for february "2 to 2" and so on. As it is now it will always say "1 to 1" because that is the values I have entered. Sub btnSkapaFil_Klicka() Dim strAnv As String Dim strBeloppJan As String Dim strBeloppFeb As String Dim strBeloppMars As String Dim strBeloppApr As String Dim strBeloppMaj As String Dim strBeloppJun As String Dim strBeloppJul As String Dim strBeloppAug As String Dim strBeloppSept As String Dim strBeloppOkt As String Dim strBeloppNov As String Dim strBeloppDec As String Dim strSavepath As String Dim intRad As Integer Dim intStartrad As Integer Dim intSlutrad As Integer Dim fs, a strOrder = Range("I2")...............
View Replies!
View Related
Create Combo Box Aligned To Grid On Worksheet
I want to create combo box 'on the fly' but i want them to be aligned vertically and horizontally to the cells of the worksheet i.e. i want it to fit into the cells exactly. I would also like it to enlarge its length depending on the size of the values (Text) it holds. How would i do this?
View Replies!
View Related
Create The Dropdown Box And Insert The Text
I am trying to make some drop down boxes, but it seems a little different than word. In word I create the driopdown box and insert the text that I want on the list. I can't seem to do this in excel. For example, I have a cell that is labled RANK, and below it, I want the drop down box to have the option AB AMN A1C and SrA. How can I create this? Once I found out, I think I can do the other combo boxes my self.
View Replies!
View Related
VBA: Format Amts Into Text $ 9.99 Type Format
Need to create a set of aligned text amts from various cells...Tried Format but unable to get right combination...I've looked at many threads and most seem related to getting amt from text instead of reverse. Cell may contain nothing or a monetary amt, negative or positive. Output needs to be in format of "$9,999.99-" or similar...with leading zeros suppressed but a min of "$ 0.00" showing, so that above/below amts with be decimal point aligned in a fixed font situation. I've almost gotten my routine finsihed but this is last remaining obstacle.
View Replies!
View Related
Insert And Format A Text Box
I would like to add a menu option that will run a subroutine to automatically add a formated text box to the worksheet at the selected cell location. The box must have the text centered, bold, underlined in Arial 10, and no border. I use this method to create a flow chart and would like to make it easier. At the moment I create three different text boxes and then copy them to the clipboard. Then I just click each one, drag it into position and edit the text. I tried recording a macro while adding the text box to the worksheet, but it didn't record anything except the cell selection.
View Replies!
View Related
Read The Name Of A Check Box (which Is In Text Format)
I am trying to generate a sub that read the name of a check box (which is in text format) in a spreadsheet in this case sheet13 and according to that ask if that check box which is in another sheet (sheet4) is true or false, I mean if it checked or not, then perform other actions But I am getting this following error Run time error ‘438’ Object doesn’t support this property or method I tried several things but always get an error. Does some body know how to make the checkboxname variable a valid name for the checkbox object in sheet4?
View Replies!
View Related
Userform Text Box Number Format
The problem is even though the worksheet cell is formated to 3 places the text box in the Userform shows many more than this. I have used the Control Source set to read the cell value. The answer I've seen 'txtValue = Format(Value, "000.00")' which seems very neat and tidy but I'm not sure how to use it or where to place it.
View Replies!
View Related
Searching Through A Worksheet For Text Or Value Entered In A Text Box
I am trying to search through an excel worksheet using the code below. I have a Form containg a text boxes and a command button (SearchTxt, FindCmd). The VBA program is able to search through XL but (1) It does not set the next value or text in a the worksheet active (2) It only finds a value or text as an entire cell and not as a set of characters in a cell. I tried to use Cells.Find method that solves the above problems, but i get an error message-Object variable not set (91) . Private Sub FindCmd_Click() Dim Rng1 As Variant If SearchTxt.Text = "" Then MsgBox "Please enter Vendor Number.", vbOKOnly, "Error" End If Set Rng1 = Range("A1:F10000").Find(what:=SearchTxt.Text, Lookat:=xlWhole, _ LookIn:=xlValues, SearchDirection:=xlNext) If Rng1 Is Nothing Then MsgBox "Cannot Find" & " " & SearchTxt.Text & ".", vbOKOnly, "Sorry" Else Rng1.Activate Exit Sub End If End Sub
View Replies!
View Related
Multiple Userform Text Box Number Format
'TextBox1.Text = Sheets("Shet1").Range("a1").Text' However I have thirty text boxes on the Userform all linked to cells on a sheet. How do I apply this so that I dont have thirty lines of code. It just does not look elegant. I'm doing this because the number on the userform shows more decimal places than the cells format. Cell formated to 3 places Userform shows about 9 mostly .999999 etc
View Replies!
View Related
Force Date Format Entry In Text Box
A form loads and the first thing the user is to do is enter a specific date in a text box (preferably in, "mm/dd/yyyy" format.) Is there any way to set up the text box so that when the form loads, the, "/"'s (slashes) are already in place? And can I set it up so that if the user enters in the date in any other manner aside from, "mm/dd/yyyy", that it will give them an error? Finally, I want the user's date input to store on a worksheet (in row, "G".) So the first time the user uses the form, they type in a date and it stores the date in the FIRST AVAILABLE CELL IN ROW, "G." (example: "G1") The next time they use the form and type in a date, it is to store in, "G2", then in, "G3", ect. Each time they fill out the form, the new entry is to save in the next row beneath the previous entry..... For other text boxes and combo boxes I have been using the following code to do this: Sheets("Sheet1").Range("A" & intRow) = Text.Value (or Combobox.Value - depending on whether it was a text box or a combo box.) Unfortunately I have NO idea how to apply this logic in regards to text boxes that are to store dates.
View Replies!
View Related
Setting Date Format For A Text Box In A User Form
I've been struggling with this for a while now and can't believe how hard it is!. I've searched on this site and on others to get some clarification but to no avail. It's pretty simple really. I have a user form which contains a tex box for a user to input the date I want the format to be dd/mm/yyyy but can't find out how to set the format of the text box to this.
View Replies!
View Related
Macro To Create Dialog Box For User Instruction
I have a workbook with 23 pages, each running a macro to calculate the final "answer". I would like to make the title page comman button create a dialog box, listing the 23 pages/macros with a check-box for each (default is "checked") asking the user which macros / calculations to execute. Any ideas how to do this? I am trying to avoid making this a sheet in my workbook.
View Replies!
View Related
Use VBA To Put Text Into A Text Box...
is there a way to use VBA to insert text into text box? I don't believe it is possible to put a formula into a text box (I have posted a question on the Excel general forum to check this), so my next thought is: can I use VBA to insert the values I want instead? Basically, I have a text box which I want to display certain text based on certain criteria.
View Replies!
View Related
Using Text Box Whilst Protecting A Worksheet
I wish to protect a worksheet to prevent loose fingered people from altering formulas, but I want the "text box" that is below these formulas (within the same worksheet) to be able to be typed in. But when I protect the worksheet, the text box cannot be typed in. Is there a way around this to allow typing in the text box whilst the sheet is protected?
View Replies!
View Related
Macro To Find Text Box #9 Enter Text
The macro recorder doesn't let me record a macro that finds text box #9, go to the end of the existing text in the text box and then add my text. When I record the macro and go to the box, it doesn't appear in the code (as well as not working).
View Replies!
View Related
Create SQL Query In VBA And Display Result As Table In Worksheet
I need the VBA script in excel that will do a SQL Query, for this case I need to select a value where there are 2-3 tags and between certain period then display the result as a table in a worksheet. Example I have a database with 3 types of tags "Tag A", "Tag B", and "Tag C", and each tag have a value with different timestamp. the database looks like this: No. Timestamp Tags Value 1 1-Jan-2010 Tag A 18 2 1-Jan-2010 Tag C 20 3 2-Jan-2010 Tag A 20 4 3-Jan-2010 Tag B 17 5 3-Jan-2010 Tag C 19 6 4-Jan-2010 Tag B 18 7 4-Jan-2010 Tag A 20 8 5-Jan-2010 Tag A 22 9 5-Jan-2010 Tag B 18 10 5-JAn-2010 Tag C 20
View Replies!
View Related
Edit The Text In The Box With VBA
I have an autoshape with a text box in it. It is one of those "scroll" shapes. I want to be able to edit the text in the box with VBA, but I don't know how to call it because I can't seem to find the properties for it. I tried recording a macro to do it, and it called it:
View Replies!
View Related
Macro Will Not Create New Worksheet And Workbook
Im trying to create a document that creates a new workbook when certain functions are selected and have written a macro to assist with this. Basically im working on a comprehensive checklist with all possible scenarios that will need to be tailored for use, so you can create a document with just what is applicable to you and not the whole checklist. I have attached a sample of what i mean, along with clearer descriptors of what the document is designed to do.
View Replies!
View Related
Create New Worksheet With Macro Buttons
Is it possible to use a macro to create a new worksheet with a button on it, that has a macro attached to it, that i can send out to people? I've looked at the Template example, but i'm not sure this will work when emailed as i'm unsure whether the template will be sent to them.
View Replies!
View Related
Macro To Access Worksheet Via Popup Box
I have a macro below that gives me a popup box that lets me type in the new sheet i want to access. etc(sheet1.xls) It give me 2 boxes one i must type the old sheet i currently access etc(sheet0.xls) and the next is the new sheet i will now be accesssing. Is there a way to change this so i only get one box that will let me type in the new sheet i want to access?
View Replies!
View Related
VBA - Userform: Click In A Text Box, Contents Is Selected
I'm building a userform (for the first time), and I'm working with a textbox control. I'd like to make it so that when the user clicks in that textbox, the contents is selected, so that if they begin typing, it will type over what is already there. My question, specifically, is whether or not there is a property I can set to make this the default behavior, and if not - what event do I tie to this action? Is it the "Enter" event? What I have now, which will probably be unacceptable to my users, is code that looks like this: Private Sub tbName_Enter() ActiveControl.Value = "" End Sub This makes the contents disappear, but I'd rather they stay there and just become "selected".
View Replies!
View Related
Macro To Create Pivot Tables And Sort Out Data In Other Worksheet
The first sheet is the row data given to me, the second one is where I want my data to be analysed automatically by macros. I managed to create a pivot table manually to give me the info I want but then I still have to copy all the results from the pivot table sheet to my analysis sheet. How can I create a macro able to do all that by it self? I tried to record a macro using the recorder but then when I try to play it it gives me an error straight at the beginning in the definition of the pivot table I think. An example of what I want the pivot table to do is: in the first sheet, go in the small table in column K and L, take the value of L1, then in the row data in column A to J, in column C look for the value in L1, once found, look for the value 1 in column J then do the average on the values in column E and put the result in my second sheet in cell F5 then do the same but look for 2 in column J and put it in F8, then 3 in F11, ... until 7 found (the data is in ss.000 and so the results in the second sheet should be formated the same way) then do the same all over again for value in L2 etc etc etc this should be done for the 6 values in column L, each having values up to 7 in the J column. Then I need to do the same for other columns and not only averages but minimums as well but I can adapt the code I think.
View Replies!
View Related
Create Button & Assign Macro When Worksheet Added
Is there a VBA code that will enable me to create a button and assign a certain macro to it everytime I insert a new worksheet? how to insert a new worksheet with VBA, what I want is that when I insert that worksheet, there is already a button there with a specific macro(already made) assigned to it.
View Replies!
View Related
Create Elipse With Text Inside Via A Macro
Is it possible to have a macro create an Elipse then place text inside the elipse. The text will be numeric and automatically count from a user entered starting value. It will only count in whole numbers from 1 to 4 digits in length (1 thru 9999). No leading or trailing zeros. I am looking to do this by clicking the mouse on an image placing the elipse/text shapes with the elipse outline ontop of the text. The worksheet will have an image covering its entirety. I recorded a couple macros but, this one, is not so easy. I may have actually found new errors never seen by mankind before. (I kid).
View Replies!
View Related
Fixing Macro: Only Create Around 53 Text File
First need to create a "Text" Folder in your desktop, then try running the macro. It will create a number of text files inside the "Text" folder.. And they contain the used cells from each row. It's supposed to create 982 text files, with the text name referred to the first column. Problem is, if you try running it, it will only create around 53 text file
View Replies!
View Related
Copy Date Into Text Box Retaining Date Format
I've created a group of user forms used for recording jobs and displaying details of jobs and have come up against a problem I think you may be able to help me with. When the user creates a new record, I've written some code to stamp txtdate text box with the current system date and this information is then copied onto a sheet called Data into a specific column. This part works fine and as the column is formatted to handle dates it displays fine. However, when I pull the data back in, it displays the date as the serial number date, not formatted as dd/mm/yy. I've tried every fix I can find to sort this but none of them seem to work. The code I use to pull the data in is as follows: ComboBox1.BoundColumn = 11 txtdate.Value = ComboBox1.Value The idea is that the user selects a job number from combobox1 and then the appropriate column is pulled into each txt box on the form. At the same time, I'm trying to do the same for the system time as well. Unsurprisingly I have a very similar problem here and use the same code as above. I've tried formatting both date and time txt boxes in the userform, the data transfer code etc etc with no success.
View Replies!
View Related
Creating Text Box Using Macro
Ok, I have a situation that I would love to have some help with. I have a command button that when clicked takes the user through a few questions via input boxes. The final objective that I need help with is a large typing area. Either the input box typing area needs to be larger or I need a text box to appear. After user is done typing and presses ok, I need what they typed to go into an area where I have merged some cells/rows to make a paragraph.
View Replies!
View Related
Contious Text Box Macro
I'm building a sheet where the user will run a macro that will pop up a text box where they will enter a number, the number will then be place in a specific row(d2:d30),then i've got a msgbox to pop up and say would you like to enter more,YorN, no exits the macro.Y will start an empty textbox on the next available row down. here is what i got so far but its pulling an complie syntax error. Private Sub textbox1_Change() Dim r As Range Dim s As String Set r = Sheet1.Range("d2:30") s = Activate.TextBox1.Value r.Value = s End Sub Private Sub message() Dim msb As intger msb = MsgBox("would you like to activate another book?", vbYesNo, "again") If msb = 6 Then Run , textbox1_Change End Sub
View Replies!
View Related
VBA Macro Code To Create Multiple Charts
I need to create 63 charts from data which I have in two columns. I want to create multiple charts using one macro. For the first chart I want it to use cells K2:K80 as the x values, and M2:M80 as the y values. For the next chart I want it to use cells K81:K159 as the x values and M81:159 as the y values. For the next chart I want it to use cells K160:K238 as the x values and M160:M238 as the y values. I want to continue this, creating a chart for every 78 cells of data, all the way until the 63rd chart which uses K4900:K4978 as the x values and M4900:M4978 as the y values. I have created the following macro by " recording." This macro generates the first chart that I want: Sub Macro5() ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$K$2:$K$80" ActiveChart.SeriesCollection(1).Values = "=Sheet1!$M$2:$M$80" ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveWindow.SmallScroll Down:=-3 End Sub How can I alter this macro to create all 63 charts?. It seems like there is an easy way to do this, but I don't use macros very much (at all).
View Replies!
View Related
Format Text Box As "dd/mm/yyyy"
i want to do is click the 'show form' button then i click the little calendar icon which displays a calendar on another form. When i click the date the format of the date goes in US stlye. I would like the format to be "dd/mm/yyyy".
View Replies!
View Related
Worksheet Format Macro Not Affecting Pasted Data
I'm currently using some pretty extensive conditional formatting macros on a worksheet (this functionality is working fine when data is directly entered into the sheet). But when i paste data from another worksheet into my "formatted" sheet, the formatting does not take affect. is there any way to "refresh" a macro?
View Replies!
View Related
|
TRACKER
