Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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 Code To Link A Text Box To A Cell In A Worksheet
I have built a userform and want the text boxes to access information from a worksheet when I click on the button that displays the userform.

View Replies!   View Related
VBA Code To Copy Data From Text Box And Input Into A Cell In Another Worksheet
This is probably far easier then I am making it but I need to take the information that is in a textbox created through the control toolbox and copy and paste that information into a cell on different tab in the workbook. I have tried the infamous google and haven't been able to find much. The excel books that I have don't really touch on the control toolbox functions.

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 A Comments Box Via VBA
I want to create a comments box whenever a cell is double clicked in sheet "Settlements"

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
Activex Combo Box Format Text
How do I format the text in an ActiveX combo box? I would like to increase the font.

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
Format Control Creating Text Box
I am trying to create a text box using the format control, however, everytime I click on the box it go to visual basic editor. I believe I'm missing something. I am not familiar with visual basic.

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
Select Text Inside Text Box On VBA Form
I have a text box that has default text in it. When I mouse down on the box I would like it to select all of the text in the box automatically. Does anyone have any code to do this?

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
Text Box Different Worksheet
I am trying to fill a textbox with a value from a different worksheet.

I can seem to get it to work whichever way i write it.

Also is there a way I could reference this from a different (closed) 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
Insert Text Box On Worksheet In 2003
how i insert text box on excel sheet.
In which i take values from user and then manipulate those values on click event of button.


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 Create New Worksheet?
I need to create new worksheet with all the rows which has qty (column A) value of 1 and above by clicking on a submit button....

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 The Clock In Worksheet Through Macro
I would like to create the clock function.

This is my function name. - Clock()

If i put this function in Cell A1. Then from now onwards cell A1 will display system current time. It will keep showing the every seconds changes.

And clock will run itself independenly.

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
VBA: Sort And Format A Worksheet After New Row Added
From the sample file, I am using the data on (start) worksheet. (Sort and format sheet) . This one is way over my head. After new data is added to the sheet via another UserForm, I want to sort the list by name and have alternating backgrounds based on grouped unique names.

View Replies!   View Related
Macro - Auto Insert And Create A Worksheet
Is that possibe to use the "templeate" sheet and lookup the "Master" sheet to create a new worksheet called "result"

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
Vba: Viewing A Cells Data Through A Text Box With Ability To Change
I have a text box. I want it to pull the data from Cell B2 and list it in the text box. The problem is that I want to be able to change the data if the user clieck inside the box and changes it.

View Replies!   View Related
Macro To Create Worksheet, Change It's Position, And Paste Date Into It
I need a macro that will create a new blank worksheet, move it to the last spot and paste the entire contents of the sheet named "master" into the newly created sheet.
I have tried to do this with the recorder but have been unable to get it to work.

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
Hyperlink - Text Box To Macro
How do I hyperlink a text box ( button ) to an existing macro?

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- Create A Macro That Will Change 1 Of The Base Salary
Lets say I have

Admin:
Base Salary
US Duties

Consultant:
Base Salary
Senior Advisor

Is there a way to create a macro that will change 1 of the Base Salary(s). I need one of them to be Base Salary - Admin, or Base Salary ' or something different for my vlookup.

Will this macro work for each different tab I have?

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
Macro To Find Cells With Perticulat Format In A Worksheet
I have a work sheet with around 50 odd sheets. What i want is to list out all the cells that are in perticular format

e.g.....

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
Copyright © 2005-08 www.BigResource.com, All rights reserved