Hi,
I am here seeking help/advice (again).
The user has Excel 2007 and uses a workbook to make determinations. The workbook has many sheets -one for each type of determination.
The user currently prints the desired sheet and fills it out by hand, doing calculations elsewhere.
I am attempting to rewrite the workbook so calculations can be done in the workbook and it can be printed AFTER it is filled out.
Since many of the sheets have columns set to different widths, merged cells were used (something I will avoid).
At this point My plan is to have a UserForm open and the user can fill in certain information. I want to have that data populate a TextBox on the active sheet.
I am have not been able to figure out how to get the calculated String to populate the TextBox.
I attached a cut-down sample of the workbook. The sheet I am working on is Sheet1 (I put some information there that might help).
I was pleased when the boss told me there is no hurry. I am to work on it in my spare time, so there is no rush.
Any ideas?
UserForm to populate TextBox -Excel 2007
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
UserForm to populate TextBox -Excel 2007
You do not have the required permissions to view the files attached to this post.
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: UserForm to populate TextBox -Excel 2007
Wow! Your boss lets you work for him in your spare time! How generous of him or her!
The line to set the text into the text box is
ActiveSheet.Shapes("TextBox 1").TextFrame.Characters.Text = CombineLines
Here is a more consistently formatted version of your code:
The line to set the text into the text box is
ActiveSheet.Shapes("TextBox 1").TextFrame.Characters.Text = CombineLines
Here is a more consistently formatted version of your code:
Code: Select all
Private Sub cbOK_Click()
Dim Project As String
Dim City As String
Dim DeterminDate As Date
Dim PlanDate As Date
Dim Reviewer As String
Dim Use1 As String, Use2 As String, Use3 As String, Use4 As String
Dim FormCount As Integer
Dim FormUse As Integer
Dim Line1 As String, Line2 As String, Line3 As String, CombineLines As String
Dim Filler1 As String, Filler2 As String
FormCount = 0
FormUse = 0
If txtProj_Name.Text <> "" Then FormCount = FormCount + 1
If txtCity.Text <> "" Then FormCount = FormCount + 1
If txtDetermine_Date.Text <> "" Then FormCount = FormCount + 1
If txtPlan_Date.Text <> "" Then FormCount = FormCount + 1
If txtReviewer.Text <> "" Then FormCount = FormCount + 1
If txtUse1.Text <> "" Then FormUse = FormUse + 1
If txtUse2.Text <> "" Then FormUse = FormUse + 1
If txtUse3.Text <> "" Then FormUse = FormUse + 1
If txtUse4.Text <> "" Then FormUse = FormUse + 1
If FormCount < 5 Then
MsgBox "Please fill out All boxes through Reviewer before proceeding"
Exit Sub
End If
Project = txtProj_Name.Text
City = txtCity.Text
DeterminDate = txtDetermine_Date.Text
PlanDate = txtPlan_Date.Text
Reviewer = txtReviewer.Text
Select Case FormUse
Case 0
MsgBox "You indicate there is no General Building Use"
Case 1
Use1 = txtUse1.Text
Case 2
Use1 = txtUse1.Text
Use2 = txtUse2.Text
Case 3
Use1 = txtUse1.Text
Use2 = txtUse2.Text
Use3 = txtUse3.Text
Case 4
Use1 = txtUse1.Text
Use2 = txtUse2.Text
Use3 = txtUse3.Text
Use4 = txtUse4.Text
End Select
' create the String for the text box
Filler1 = " "
Filler2 = " "
Line1 = "Name of Project: " & Project & Filler1 & "City: " & City
Line2 = "Determination Date: " & DeterminDate & Filler2 & _
" Plan Date: " & PlanDate & Filler2 & " Reviewer: " & Reviewer
CombineLines = Line1 & vbCr & Line2
ActiveSheet.Shapes("TextBox 1").TextFrame.Characters.Text = CombineLines
Unload Me
End Sub
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: UserForm to populate TextBox -Excel 2007
Many Thanks Hans!
Needless to say, your Select Case section is MUCH nicer than what I cobbled together.
I will continue on with the building process and see how it looks. I still need to add the General Building Use section to the TextBox.
Have a Great day, and Thanks again for the help.
Needless to say, your Select Case section is MUCH nicer than what I cobbled together.
I will continue on with the building process and see how it looks. I still need to add the General Building Use section to the TextBox.
Have a Great day, and Thanks again for the help.
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: UserForm to populate TextBox -Excel 2007
Another way to handle the use part would be
or you could work directly with txtUse1 ... txtUse4 instead of using variables Use1 ... Use4.
Code: Select all
If FormUse = 0 Then
MsgBox "You indicate there is no General Building Use"
Else
Use1 = txtUse1.Text
End If
If FormUse >= 2 Then
Use2 = txtUse2.Text
End If
If FormUse >= 3 Then
Use3 = txtUse3.Text
End If
If FormUse >= 4 Then
Use4 = txtUse4.Text
End If
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: UserForm to populate TextBox -Excel 2007
Thanks Hans
Is there a way to set the Font and Font Size that is used? I am working on getting the spacing between Project and City so it fills the top line of the text box.
I hope tol calculate the size of Project and City and load enough spaces between them so City appears on far right.
I think it would be overly ambitious to attempt Bolding the titles of the fields....
Is there a way to set the Font and Font Size that is used? I am working on getting the spacing between Project and City so it fills the top line of the text box.
I hope tol calculate the size of Project and City and load enough spaces between them so City appears on far right.
I think it would be overly ambitious to attempt Bolding the titles of the fields....
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: UserForm to populate TextBox -Excel 2007
It's not difficult to set the font and font size:
But computing the space needed to make the text appear filled out would be very complicated.
Wouldn't it be easier to fill cells on the worksheet instead of using a text box? You can place parts of the text in different cells, and control their formatting the way you want.
Code: Select all
With ActiveSheet.Shapes("TextBox 1").TextFrame.Characters.Font
.Name = "Comic Sans MS"
.Size = 18
End With
Wouldn't it be easier to fill cells on the worksheet instead of using a text box? You can place parts of the text in different cells, and control their formatting the way you want.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: UserForm to populate TextBox -Excel 2007
Thanks Hans
Yes it might be easier to have the info loaded into cells. I will know more when I get deeper into what has to be done with column widths for all the sheets.
The TextBox approach was an idea I had that would not be affected by the various column widths. This whole thing may end up being a "Wash-Out", but it is fun to see if it can be improved. As they are doing it now, they could just as easily use Word...
I assume by your comment that it is not easy to determine the length of an entry in the UserForm (nor of the variable where it is stored). OK I will go for "Close Enough" for now.
Thanks again for taking the time.
Yes it might be easier to have the info loaded into cells. I will know more when I get deeper into what has to be done with column widths for all the sheets.
The TextBox approach was an idea I had that would not be affected by the various column widths. This whole thing may end up being a "Wash-Out", but it is fun to see if it can be improved. As they are doing it now, they could just as easily use Word...
I assume by your comment that it is not easy to determine the length of an entry in the UserForm (nor of the variable where it is stored). OK I will go for "Close Enough" for now.
Thanks again for taking the time.
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: UserForm to populate TextBox -Excel 2007
Merged cells are unattractive since they don't play nice with VBA, but applying "Center Across Selection" alignment to a range of cells doesn't cause problems since it's only a matter of formatting, the cells aren't actually merged. So Center Across Selection could be an attractive option to display text across multiple cells.
It is easy to determine the length of a string in characters, but unless you use a fixed-width font such as Courier New or Consolas, it's not so easy to determine the width of the formatted string in pixels or points.
It is easy to determine the length of a string in characters, but unless you use a fixed-width font such as Courier New or Consolas, it's not so easy to determine the width of the formatted string in pixels or points.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: UserForm to populate TextBox -Excel 2007
That was why I used "Center Across Seelction" for row 1 (SAC DETERMINATION WORKSHEET).
I think it might be hard to pick the cells to center across for the Dates and Reviewer information.
Or maybe I am just not thinking of how to acomplish it. Are you suggesting 3 seperate entries:
"Determination Date: " & txtDetermine_Date.text -This one goes in Column A (that one is easy)
"Plan Date: " & txtPlan_Date.text -Not sure which column wil always work here
"Reviewer " & txtReviewer.Text -same as above
However, I just realized that I do not have to do this first. I can create each of the sheets required with the columns sized the way they are needed,
and THEN put the information at the top of each sheet, since then I would KNOW which columns to use. Hmmmmmm
btw How do you determine the length of the string contained in a variable (without saving it to a cell first)? ;-)
I think it might be hard to pick the cells to center across for the Dates and Reviewer information.
Or maybe I am just not thinking of how to acomplish it. Are you suggesting 3 seperate entries:
"Determination Date: " & txtDetermine_Date.text -This one goes in Column A (that one is easy)
"Plan Date: " & txtPlan_Date.text -Not sure which column wil always work here
"Reviewer " & txtReviewer.Text -same as above
However, I just realized that I do not have to do this first. I can create each of the sheets required with the columns sized the way they are needed,
and THEN put the information at the top of each sheet, since then I would KNOW which columns to use. Hmmmmmm
btw How do you determine the length of the string contained in a variable (without saving it to a cell first)? ;-)
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: UserForm to populate TextBox -Excel 2007
For those texts, you shouldn't use Center Across Selection because you don't really want the text to be centered. Instead, you can use the fact that left-aligned text will be displayed across the cell(s) to the right if it's too long for the cell that contains it AND if those cells to the right are empty. Similarly, right-aligned text will be displayed across the cell(s) to the left if it's too long for the cell that contains it AND if those cells to the left are empty.So it's a matter of trial and error to determine what space you'd need for the longest text you need to accomodate.
A string variable contains plain, unformatted text, so you can only determine the number of characters it contains:
Dim strReviewer As String
strReviewer = "Reviewer " & txtReviewer.Text
MsgBox Len(strReviewer)
will display the total number of characters in strReviewer. But you can't determine the width of the text, since this width depends on the formatting (font name, font size, font style), and the string variable doesn't contain any formatting information.
A string variable contains plain, unformatted text, so you can only determine the number of characters it contains:
Dim strReviewer As String
strReviewer = "Reviewer " & txtReviewer.Text
MsgBox Len(strReviewer)
will display the total number of characters in strReviewer. But you can't determine the width of the text, since this width depends on the formatting (font name, font size, font style), and the string variable doesn't contain any formatting information.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: UserForm to populate TextBox -Excel 2007
Here I am laughing at myself -I often try to make things more complicated than need be.
I can left align the Name of Project string and right align the City String.
Because the dates should be fairly uniform in size I am left align the string containing both dates and right align the Reviewer string.
(The textbox would have been more fun (if I could get it to work properly) LOL
Thanks Hans -I probably will not be entertaining you will requests for a little while now.
I can left align the Name of Project string and right align the City String.
Because the dates should be fairly uniform in size I am left align the string containing both dates and right align the Reviewer string.
(The textbox would have been more fun (if I could get it to work properly) LOL
Thanks Hans -I probably will not be entertaining you will requests for a little while now.
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: UserForm to populate TextBox -Excel 2007
I decided to give the TextBox idea a rest. I changed the approach to creating strings based on the user input and puting them in cells.
For the General Building Use string, I would like to have the code insert a special char before each different Use. Character code 25A1 is supposed to be an empty box. That is what I would like to insert in the string that will be entered in the cell. I tried recording a macro but it does not pick it up. I did not have luck searching the site either. Can anyone tell me how to insert a box into the text string that a variable contains?
Thanks
For the General Building Use string, I would like to have the code insert a special char before each different Use. Character code 25A1 is supposed to be an empty box. That is what I would like to insert in the string that will be entered in the cell. I tried recording a macro but it does not pick it up. I did not have luck searching the site either. Can anyone tell me how to insert a box into the text string that a variable contains?
Thanks
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: UserForm to populate TextBox -Excel 2007
By default, VBA works with ANSI strings (character codes 0...255). The box character that you mention is a Unicode character. To include such a character in a string, you must use the ChrW function (the W stands for Wide since Unicode characters are 2 bytes "wide" while ANSI characters are 1 byte only).
Example:
Range("A2") = ChrW(&H25A1) & " Hello World!"
or
Range("A2") = ChrW(9633) & " Hello World!"
Hexadecimal 25A1 = decimal 9633.
Example:
Range("A2") = ChrW(&H25A1) & " Hello World!"
or
Range("A2") = ChrW(9633) & " Hello World!"
Hexadecimal 25A1 = decimal 9633.
Best wishes,
Hans
Hans