VBA Form Formatting

[Steve]

I've designed a form to help keep track of quantites as opposed to hours. Its
got 3 labels and 3 corresponding text boxes, Task Name, Estimated Quantity,
and Completed Quantity. The labels are; txtqtype, txtqest, txtqcom
respectively. There is an ok and cancel button as well.

Im not a regualr programmer but I was able to figure out how to make it do
what I want it to do. I figured the formatting would be the easy stuff. I was
wrong. It is supposed to read the information in the Task Name field in
Projects, truncate the part of the string in parenthesis and print the
remainder into the text box txtqtype. For txtQest it reads the string and
truncates everything outside of the parenthesis the prints the value to the
textbox txtqest.

I want that string to be displayed when I start the form up and the cursor
to defualt on the bottom textbox txtqcom. This is what I have as far as
reading and displaying the string.

Private Sub txtQtype_Enter()
Dim Qtype, Qvar, Qstop

Qvar = ActiveCell

If Qvar = Empty Then
txtQtype.Locked = False
Else
txtQtype.Locked = True
txtQtype.BackColor = RGB(180, 180, 180)
End If

Qstop = InStr(1, Qvar, "(", vbTextCompare)
If Qstop = 0 Then
Qtype = Qvar
txtQtype.Text = Qtype
Else
Qtype = Left(Qvar, Qstop - 1)
txtQtype.MaxLength = Qstop - 1
txtQtype.Text = Qtype

End If


End Sub


I dont even know if I should be using the Enter event. But its the only
thing I can do to get even close to what I want to happen. If someone could
help me out with this I'd appriciate it. Sorry its not commented. Also I'd
like to know how to make pressing the Enter button when in a textbox function
the same as clicking OK.

 

[John]

I've designed a form to help keep track of quantites as opposed to hours. Its
got 3 labels and 3 corresponding text boxes, Task Name, Estimated Quantity,
and Completed Quantity. The labels are; txtqtype, txtqest, txtqcom
respectively. There is an ok and cancel button as well.

Im not a regualr programmer but I was able to figure out how to make it do
what I want it to do. I figured the formatting would be the easy stuff. I was
wrong. It is supposed to read the information in the Task Name field in
Projects, truncate the part of the string in parenthesis and print the
remainder into the text box txtqtype. For txtQest it reads the string and
truncates everything outside of the parenthesis the prints the value to the
textbox txtqest.

I want that string to be displayed when I start the form up and the cursor
to defualt on the bottom textbox txtqcom. This is what I have as far as
reading and displaying the string.

Private Sub txtQtype_Enter()
Dim Qtype, Qvar, Qstop

Qvar = ActiveCell

If Qvar = Empty Then
txtQtype.Locked = False
Else
txtQtype.Locked = True
txtQtype.BackColor = RGB(180, 180, 180)
End If

Qstop = InStr(1, Qvar, "(", vbTextCompare)
If Qstop = 0 Then
Qtype = Qvar
txtQtype.Text = Qtype
Else
Qtype = Left(Qvar, Qstop - 1)
txtQtype.MaxLength = Qstop - 1
txtQtype.Text = Qtype

End If


End Sub


I dont even know if I should be using the Enter event. But its the only
thing I can do to get even close to what I want to happen. If someone could
help me out with this I'd appriciate it. Sorry its not commented. Also I'd
like to know how to make pressing the Enter button when in a textbox function
the same as clicking OK.

Steve,
What version of Project are you using? In Project VBA there are several
"change" events but as far as I know, no "enter" event. What you have
shown in your sample code is a private sub called "txtQtype_Enter" -
that does not constitute an enter event - it is simply the name of your
private sub. Writing VBA code using events can sometimes give unexpected
results - like triggering where or when you don't want it to fire.

When you say you created a form I assume you mean a userform. How is
that userform called (i.e. what causes it to be displayed)? If not a
userform, what "form" did you create?

You say you want certain information displayed in a form but what
exactly is your end goal? A userform may not be the best approach. If we
knew your end goal we can help find the best approach for you.

And yes, formatting using VBA seems like something that should be simple
and straightforward but as you discovered it is one of the most tedious
and troublesome parts of code - been there, done that.

By the way, I'm not a "regualr" programmer either. I may not even be a
"regular" programmer, but nonetheless, I'll try to help. Sorry, I
couldn't resist

John
Project MVP

 

[Steve]

Steve,
What version of Project are you using? In Project VBA there are several
"change" events but as far as I know, no "enter" event. What you have
shown in your sample code is a private sub called "txtQtype_Enter" -
that does not constitute an enter event - it is simply the name of your
private sub. Writing VBA code using events can sometimes give unexpected
results - like triggering where or when you don't want it to fire.

I am using project 07. From what I understand, Enter event happens when the
focus is on the particular object. But it is an actual event.

When you say you created a form I assume you mean a userform. How is
that userform called (i.e. what causes it to be displayed)? If not a
userform, what "form" did you create?

You say you want certain information displayed in a form but what
exactly is your end goal? A userform may not be the best approach. If we
knew your end goal we can help find the best approach for you.

The form is called when via macro. I was instructed not to use the custom
fields. Something about too many departments will want to use custom fields
and with our work on project server it would be to difficult to assign each
department their own custom field. While I don't agree with the difficulty, I
just wrote this program to read the task name and calculate %complete of a
quantity while keeping the numeric data in the task name field after the
actual task name.

For example, a task name would be Dig Holes. There are 25 holes that need to
be dug, therefore, my Task Name field would read Dig Holes (0/25). Like I
said before. I activate my macro and call the form. There are three
textboxes. The top is labeled Task Name: the next labeled denominator: and
the third is labeled numerator:

What I would like is for the form to read the Task Name field and put it in
the Task Name text box (txtQtype in my code) and lock the box if there is
data in the Task Name. I want it to do the same thing for the denominator,
leaving only the numerator open and slected. I can actually do all that
already. My code in my first post works great. However, I cannot make the
data appear in the textboxes when the form loads. As it is now, the data
doesnt appear until I tab trough the box.

 

[Jack Dahlgren MVP]

Use the "Initialize" event for the form to populate your text boxes.
Then you can use a "Change" event if you want them to change after that.

-Jack

 

[Jack Dahlgren MVP]

Steve,
What version of Project are you using? In Project VBA there are several
"change" events but as far as I know, no "enter" event. What you have
shown in your sample code is a private sub called "txtQtype_Enter" -
that does not constitute an enter event - it is simply the name of your
private sub. Writing VBA code using events can sometimes give unexpected
results - like triggering where or when you don't want it to fire.

John,

When you build a form in VBA each of the controls on the form (textboxes,
labels, etc.) all have a set of events like enter, change, mouseover etc.
The form itself has a number of events.This sounds to me like what he is
doing. I find that these events are much more reliable than the project
level events, though I have found some bugs in them too. In this case he
wants to set values when the form opens so if the form were called
fmStevesForm he would use "fmStevesForm_Initialize()" and set up the values
in the text boxes at that time.

-Jack Dahlgren

 

[Steve]

Use the "Initialize" event for the form to populate your text boxes.
Then you can use a "Change" event if you want them to change after that.

-Jack

Should I enter all my code under
Private Sub frmQupdate_Initialize() ?

I have tried this and it doesn't work for some reason. I run the macro and
the form pops up but no data is in the text boxes. I know the code works, I
have tested it with message boxes and by using the enter event. If you'd like
I can post the code. Btw, I looked at a few of your macros on your web site
and they pointed me in the right direction. I couldn't use your stuff because
it was a bit different than what I needed. But I incorporated a few of the
macros into one and added some other stuff that I needed.

 

[Steve]

So it worked. But I used Private Sub UserForm_Initialize()

I don't really understand why it worked that way. My form is called
frmQupdate, but oh well. Thanks for the help.

 

[John]

John,

When you build a form in VBA each of the controls on the form (textboxes,
labels, etc.) all have a set of events like enter, change, mouseover etc.
The form itself has a number of events.This sounds to me like what he is
doing. I find that these events are much more reliable than the project
level events, though I have found some bugs in them too. In this case he
wants to set values when the form opens so if the form were called
fmStevesForm he would use "fmStevesForm_Initialize()" and set up the values
in the text boxes at that time.

-Jack Dahlgren

Jack,
You know, I didn't think to look at the events associated with
userforms. Had I done that I probably would have understood what Steve
was getting at. Nonetheless, you jumped in and it looks like he's on his
way to his end goal.

John

 

[Jack Dahlgren MVP]

If it works then don't question why
Just be grateful.

-Jack