sometimes it takes one variable, sometimes another ...

[Larry Levinson]

Given the following:

sub start()
Dim this_day, MyMonth, MyDay, MyYear, MyWeekDay, This_Month
this_day = Date
MyWeekDay = Weekday(this_day)
Select Case MyWeekDay
Case 1
this_day = this_day - 2
Case 2
this_day = this_day - 3
Case 3
this_day = this_day - 1
Case 4
this_day = this_day - 1
Case 5
this_day = this_day - 1
Case 6
this_day = this_day - 1
Case 7
this_day = this_day - 1
End Select

MyMonth = Month(this_day)
MyDay = Day(this_day)
MyYear = Year(this_day)

Select Case MyMonth
Case 1
This_Month = "Jan."
Case 2
This_Month = "Feb."
Case 3
This_Month = "March"
Case 4
This_Month = "April"
Case 5
This_Month = "May"
Case 6
This_Month = "June"
Case 7
This_Month = "July"
Case 8
This_Month = "Aug."
Case 9
This_Month = "Sept."
Case 10
This_Month = "Oct."
Case 11
This_Month = "Nov."
Case 12
This_Month = "Dec."
End Select


Load dayprompt
dayprompt.the_Month.Text = This_Month
dayprompt.the_day.Value = MyDay
dayprompt.the_Year.Text = MyYear
dayprompt.Show
end sub

dayprompt the form has three list boxes, the_Month, the_Day, the_year
....

It initalizes the value for either the_Month or the_Year, but never
both at the same time -- unless you click on the list box -- and it
always does the the_Day.

suggestions please ...
thanks in advance.


Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)

 

[Bob Phillips]

I don't get this. It fails for me as Listboxes don't have a text property,
and if I change them to Value, the form shows empty.

What exactly are you trying to achieve?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

dayprompt the form has three list boxes, the_Month, the_Day, the_year

Tell us how you have the list boxes set up. Are the list values on a
spreadsheet somewhere, or do you add them programmatically? Are the values
you're trying to set the .Text property to actually in the lists?

Where does start() get called from?

And you're not getting any errors--just sporadic results?

 

[Larry Levinson]

The RowSource property for each of
Listbox the_month,
listbox the_day,
listbox the_year

is set to a column on my spreadsheet.
ie:
Mo. Day Year
F G H
1 Jan. 1 2005
2 Feb. 2 2006
3 March 3 2007
4 April 4 2008
5 May 5
6 June 6
7 July 7
8 Aug. 8
9 Sept. 9
10 Oc.t 10
11 Nov. 11
12 Dec. 12
13 13
14 14
etc. to 31



Start is the name of the macro to begin ... Tools|Macro|Macro ...
start

No errors, but the variables are not being stored/passed/used UNLESS,
once the user form loads, you make a change IN the listboxes. THEN, it
takes the values in the listboxes.

Tell us how you have the list boxes set up. Are the list values on a
spreadsheet somewhere, or do you add them programmatically? Are the values
you're trying to set the .Text property to actually in the lists?

Where does start() get called from?

And you're not getting any errors--just sporadic results?

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)

 

[Larry Levinson]

I don't get this. It fails for me as Listboxes don't have a text property,
and if I change them to Value, the form shows empty.

What exactly are you trying to achieve?

Populate three list boxes on a form with Month, day, year, but also
give the user the opportunity to use an index (columns on a
spreadsheet) to adjust the date(s) if the infomation they want is tied
to a date OTHER than the one I have calculated.


the user will be updating data once a day. However, the possiblity
exists that they will fail to do the routine each day, and then will
have to go back and do two or three days in quick succession ...
Hence, I want to show what I believe to be the day they should be
working on, but also give them the option to change to another day in
the list index.

My real problem seems to be once I get the form open here ...

Private Sub StoryCount_Click()
Dim k1 As String, k2 As String, k3 As String, k4 As String, counter As
Long, Screen As Integer, this_day As String
Dim MyMonth, MyDay, MyYear, This_Month

MyMonth = dayprompt.the_Month.Text
MyDay = dayprompt.the_day.Value
MyYear = dayprompt.the_Year.Value
^^^^^^^

dayprompt.the_Year.Value is empty UNTIL you change the value displayed
in the listbox and reset.



Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)

 

[Guest]

No errors, but the variables are not being stored/passed/used UNLESS,
once the user form loads, you make a change IN the listboxes. THEN, it
takes the values in the listboxes.

Now I see it. I built a spreadsheet to duplicate what you're doing, using
the code you posted. I set the control source of each of the controls to a
cell on the spreadsheet, and those cells get assigned the right values when
the form loads. But not every time.

Sometimes when I run start(), only one or two of the listboxes appears with
a highlighted selection and a value in the controlsource cell. If I repeat
the macro a couple of times, I can usually get all the listboxes to operate.

When things don't work, there's a grey box around the value in a textbox
that should be selected, but it isn't selected (highlighted.) And in the
immediate window, when I ask for the .Value or .Text of the faulty listbox, I
get nothing, even though they should have been set. Something funky is going
on with the events here.

I can reset to the problematic mode by clearing the controlsource cells and
changing the rowsource of one of the boxes. That seems to generate the
fault. But by running the macro enough times, I can get it to behave
properly.

I'm curious about what's happening. Let me know if you find an explanation.

BTW, Excel 2003 here.

 

[Tom Ogilvy]

I suspect you are getting some wierd interaction with recalculation on the
worksheet - why not just use a textbox and a spin button and let the user
dial in the date they want to use.

If you want to pursue your current approach, you should break your tie to
the sheet (rowsource and control source should be cleared) and use code top
populate your listboxes and return results.

 

[Larry Levinson]

now that is an idea. one reason I have been trying to do date
calculations is that I have other `doohickeys' that run into the same
problem and I am looking for a solution on how to calculate
'yesterday' across weekends when you can't assume the data is being
updated on Monday. thanks.

I suspect you are getting some wierd interaction with recalculation on the
worksheet - why not just use a textbox and a spin button and let the user
dial in the date they want to use.

If you want to pursue your current approach, you should break your tie to
the sheet (rowsource and control source should be cleared) and use code top
populate your listboxes and return results.

If I had a really good sig file, this is where it would go ....

 

[Larry Levinson]

yes, no errors. just that sometimes the listbox will pass the contents
to the variable during execution and sometimes it won't. and sometimes
its the month, sometimes its the day and sometimes its the year that
fails to get passed. However, if I click on each box, it takes the
contents ....

Thanks.

Tell us how you have the list boxes set up. Are the list values on a
spreadsheet somewhere, or do you add them programmatically? Are the values
you're trying to set the .Text property to actually in the lists?

Where does start() get called from?

And you're not getting any errors--just sporadic results?

If I had a really good sig file, this is where it would go ....

 

[Tom Ogilvy]

yesterday = date - choose(weekday(date),2,3,1,1,1,1,1)

 

[Larry Levinson]

coool ... On Sat, 5 Mar 2005 16:49:01 -0500, "Tom Ogilvy"

yesterday = date - choose(weekday(date),2,3,1,1,1,1,1)

If I had a really good sig file, this is where it would go ....

 

[Larry Levinson]

this appears to be working correctly, thanks for your help:
this_day = Date
yesterday = this_day - Choose(Weekday(this_day), 2, 3, 1, 1, 1, 1, 1)

MyMonth = Month(yesterday)
MyDay = Day(yesterday)
MyYear = Year(yesterday)

Select Case MyMonth
Case 1
This_Month = "Jan."
Case 2
This_Month = "Feb."
Case 3
This_Month = "March"
Case 4
This_Month = "April"
Case 5
This_Month = "May"
Case 6
This_Month = "June"
Case 7
This_Month = "July"
Case 8
This_Month = "Aug."
Case 9
This_Month = "Sept."
Case 10
This_Month = "Oct."
Case 11
This_Month = "Nov."
Case 12
This_Month = "Dec."
End Select

Load dayprompt
dayprompt.the_Month.RowSource = ""
dayprompt.the_day.RowSource = ""
dayprompt.the_Year.RowSource = ""
' Add months to the listbox
For Row = 1 To 12
dayprompt.the_Month.AddItem Sheets("dataholder").Cells(Row, 6)
Next Row
' Add days to the listbox
For Row = 1 To 31
dayprompt.the_day.AddItem Sheets("dataholder").Cells(Row, 7)
Next Row
' Add years to the listbox
For Row = 1 To 5
dayprompt.the_Year.AddItem Sheets("dataholder").Cells(Row, 8)
Next Row

dayprompt.the_Month.Value = This_Month
dayprompt.the_day.Value = MyDay
dayprompt.the_Year.Value = MyYear

coool ... On Sat, 5 Mar 2005 16:49:01 -0500, "Tom Ogilvy"


If I had a really good sig file, this is where it would go ....

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)

 

[Larry Levinson]

i spoke too soon. same problem on the `day.'

this appears to be working correctly, thanks for your help:
this_day = Date
yesterday = this_day - Choose(Weekday(this_day), 2, 3, 1, 1, 1, 1, 1)

MyMonth = Month(yesterday)
MyDay = Day(yesterday)
MyYear = Year(yesterday)

Select Case MyMonth
Case 1
This_Month = "Jan."
Case 2
This_Month = "Feb."
Case 3
This_Month = "March"
Case 4
This_Month = "April"
Case 5
This_Month = "May"
Case 6
This_Month = "June"
Case 7
This_Month = "July"
Case 8
This_Month = "Aug."
Case 9
This_Month = "Sept."
Case 10
This_Month = "Oct."
Case 11
This_Month = "Nov."
Case 12
This_Month = "Dec."
End Select

Load dayprompt
dayprompt.the_Month.RowSource = ""
dayprompt.the_day.RowSource = ""
dayprompt.the_Year.RowSource = ""
' Add months to the listbox
For Row = 1 To 12
dayprompt.the_Month.AddItem Sheets("dataholder").Cells(Row, 6)
Next Row
' Add days to the listbox
For Row = 1 To 31
dayprompt.the_day.AddItem Sheets("dataholder").Cells(Row, 7)
Next Row
' Add years to the listbox
For Row = 1 To 5
dayprompt.the_Year.AddItem Sheets("dataholder").Cells(Row, 8)
Next Row

dayprompt.the_Month.Value = This_Month
dayprompt.the_day.Value = MyDay
dayprompt.the_Year.Value = MyYear











Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)