Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Dialog Box question

  Asked By: Marty    Date: Feb 21    Category: MS Office    Views: 790
  

I was wondering if it was possible to have my custom dialog box
pull items from my worksheet and display them. Specifically, we have
an excel timecard system, and we run a script to export it to access.
When the script is done running, i want it to display a dialog box
that has the total hours worked (which i have in a cell), the total
vacation and sick time, which I would have to generate (if a cell in
column F is equal to Sick time, the corresponding value from column V
should be returned).

Is this possible? Can someone point me to a tutorial that would show
me how to pull these values? Thanks!

Share: 

 

7 Answers Found

 
Answer #1    Answered By: Keiko Mori     Answered On: Feb 21

By "dialog box" do you mean "userform"?
If so, have you looked at the properties panel for the
"fields" in your userform?
each of them have a property called "value" or "text".
If you set this property to the value of the cell  contents,
then you've just populated your userform.

Usually, you set these in the userform_initialize macro.

I know this is vague, but it's really very intuitive once
you know where to look.

So... let's start with the first question.
Is your "dialog box" a "userform" ??
Or are you using a MsgBox to just display  the results
of the script?
A message box  can be shown as:
TotalHrs = cells(4,3) '(or whatever)
TotalVac = Cells(4,5) '(or whatever)
TotalSick = Cells(4,6)

Msg = "Total Hours worked: " & TotalHrs & chr(13)
Msg = Msg & "Total vacation Time: " & TotalVac & chr(13)
Msg = Msg & "Total Sick Time: " & TotalSick
Msgbox Msg

Of course, there is the possibility that I'm totally misunderstanding the
question.

let me know if I can be more vague... er... specific...

 
Answer #2    Answered By: Code Guru     Answered On: Feb 21

I meant userform. So I inserted a text box  into it, and
created the command buttons i need, and I want it to say
"Total hours" [value of cell]
And then
"Total vacation hours" [which has to be calculated]
etc

and since its obvious i really have next to no idea what i'm doing...
where is the initialize macro?

 
Answer #3    Answered By: Qadriyyah Malik     Answered On: Feb 21

I didn't say it was "obvious".. I said it was "intuitive".
Meaning that once you see it, you can figure out how it works!

What we're talking about here is called "Event Programming"
(or "Event Management", or something similar, with the word "Event" in the
title)
Changing a value is an "Event", Changing a SELECTION is an "Event".
Initializing a UserForm is an "Event"... There are lots of "Events".
You just have to figure out which Event you want to use to control how your form
acts.

If you're looking at the userform (in the VBA Editor's "Design" window).
right-click on the form and select "view code".
I think the DEFAULT macro for the form itself is the Initialize event.
If you have the textbox selected, right-click/View Code defaults to the Change
event.
If not, use the pull-down on the top right to find it.
Let's say your userform is called (unimaginatively(grin)) UserForm1.
and your textbox is TextBox1.
your worksheet  is called "Sheet1" and
the cell  you want the value from is Row 4, column  3.

In the Initialize macro, you can use:

UserForm1.TextBox1.Text = WorkSheets("Sheet1").Cells(4,3)

I can't test it right now, I've got a macro running  for the next 90 minutes or
so,
but I'm pretty sure it's .Text, or maybe .Value

You can do the calculation here too. Like:
.Text = WorkSheets("Sheet1").Cells(4,3) - WorkSheets("Sheet1").Cells(4,4)


If you'd like me to take a closer look, send me a file....

 
Answer #4    Answered By: Terry Williams     Answered On: Feb 21

Thanks, I think that should do me...... I can't send out the
spreadsheet because it contains confidential info, but if i keep
having trouble i can clean one up and send it... but hopefully, that
should be enough info for me to do it myself...

 
Answer #5    Answered By: Casey Montgomery     Answered On: Feb 21

So i have all the variables calculated, and I slammed
them in a messagebox to make sure they are working and formatted
correctly, but I'm not sure how to get them in the text box  of my
userform (because that is where I really want the text). Also, is it
possible to format certain lines in the text box but not others?
specifically, i'd like vacation, sick, and paidleave lines to be red
or bold...

Private Sub UserForm_Initialize()
Dim totalhours As Integer, totalvacation As Integer, totalsick As
Integer
Dim totalotherpaidleave As Integer, remainder As Integer
totalvacation = 0
totalsick = 0
totalotherpaidleave = 0
remainder = 0
Dim r As Integer

totalhours = Range("V29").Value

For r = 13 To 27 ' the rows in the worksheet  to find values  in
If Range("Timesheet!J" & r).Value = "Bereavement" Then
totalotherpaidleave = totalotherpaidleave +
Range("Timesheet!V" & r).Value
End If 'Ends If loop, which makes it so only =Bereavement
values are acted on.
If Range("Timesheet!J" & r).Value = "Holiday" Then
totalotherpaidleave = totalotherpaidleave +
Range("Timesheet!V" & r).Value
End If 'Ends If loop, which makes it so only =Holiday values
are acted on.
If Range("Timesheet!J" & r).Value = "Jury Duty" Then
totalotherpaidleave = totalotherpaidleave +
Range("Timesheet!V" & r).Value
End If 'Ends If loop, which makes it so only =Jury Duty
values are acted on.
If Range("Timesheet!J" & r).Value = "Other Paid
Time Off" Then
totalotherpaidleave = totalotherpaidleave +
Range("Timesheet!V" & r).Value
End If 'Ends If loop, which makes it so only =Other Paid
Time Off values are acted on.
If Range("Timesheet!J" & r).Value =
"Paternity/Maternity Leave" Then
totalotherpaidleave = totalotherpaidleave +
Range("Timesheet!V" & r).Value
End If 'Ends If loop, which makes it so only
=Paternity/Maternity Leave values are acted on.
If Range("Timesheet!J" & r).Value = "Sick Leave" Then
totalsick = totalsick + Range("Timesheet!V" & r).Value
End If 'Ends If loop, which makes it so only =Sick Leave
values are acted on.
If Range("Timesheet!J" & r).Value = "Vacation" Then
totalvacation = totalvacation + Range("Timesheet!V" &
r).Value
End If 'Ends If loop, which makes it so only =Holiday values
are acted on.
Next 'Iterates r from 3 to 17, and indicates end of For loop

remainder = totalhours - (totalvacation + totalsick +
totalotherpaidleave)

MsgBox "The following is a summary of the hours exported" & vbCr _
& vbTab & "Total hours: " & vbTab & vbTab & totalhours & vbCr _
& vbTab & "Total vacation: " & vbTab & vbTab & totalvacation
& vbCr _
& vbTab & "Total sick leave: " & vbTab & vbTab & totalsick &
vbCr _
& vbTab & "Total other paid leave: " & vbTab &
totalotherpaidleave & vbCr _
& vbTab & "----------------------------" & vbTab & "---" &
vbCr _
& vbTab & "Total normal hours: " & vbTab & remainder
End Sub

 
Answer #6    Answered By: Jonathan Brown     Answered On: Feb 21

Why are you using a textbox to display  this?
Are you expecting the user to be able to change the values?
or is it supposed to be Display Only?

Second,
A Textbox is designed to be single-line.
For multiple lines, you'd use a listbox.

If what you're really after is a dressed-up Msgbox,
I'd use Labels instead.
You can then change the font and forecolor of the label to dress it up.

so.. the question  is, how do you want this userform to be USED.
Do you want them to be able to select and/or change the values?

 
Answer #7    Answered By: Zobebah Mizrachi     Answered On: Feb 21

Yes, what i'm after is a dressed up messagebox... within a label is it
possible to change the colors of one line? i've figured out how to use
initialize to calculate my values, but how do i get them in the list box?

 
Didn't find what you were looking for? Find more on Dialog Box question Or get search suggestion and latest updates.




Tagged: