UserForm to populate TextBox -Excel 2007

[Reimer]

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?

[HansV]

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:

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

[Reimer]

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.

[HansV]

Another way to handle the use part would be

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

or you could work directly with txtUse1 ... txtUse4 instead of using variables Use1 ... Use4.

[Reimer]

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

[HansV]

It's not difficult to set the font and font size:

Code: Select all

With ActiveSheet.Shapes("TextBox 1").TextFrame.Characters.Font
  .Name = "Comic Sans MS"
  .Size = 18
End With

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.

[Reimer]

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.

[HansV]

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.

[Reimer]

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)? ;-)

[HansV]

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.

[Reimer]

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.

[Reimer]

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

[HansV]

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.