Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sometimes it takes one variable, sometimes another ...
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 ....) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sometimes it takes one variable, sometimes another ...
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) "Larry Levinson" wrote in message ... 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 ....) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sometimes it takes one variable, sometimes another ...
"Larry Levinson" wrote:
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sometimes it takes one variable, sometimes another ...
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 Shawn O'Donnell wrote: 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. "Larry Levinson" wrote: 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 Talking up to the vocal ... LLevinson*Bloomberg.net (remove the star etc ....) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sometimes it takes one variable, sometimes another ...
"Bob Phillips" wrote:
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 ....) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
sometimes it takes one variable, sometimes another ...
"Larry Levinson" wrote:
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
sometimes it takes one variable, sometimes another ...
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. -- Regards, Tom Ogilvy "Larry Levinson" wrote in message ... "Bob Phillips" wrote: 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 ....) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
sometimes it takes one variable, sometimes another ...
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. On Sat, 5 Mar 2005 11:35:12 -0500, "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Larry Levinson" wrote in message .. . "Bob Phillips" wrote: 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 ....) If I had a really good sig file, this is where it would go .... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
sometimes it takes one variable, sometimes another ...
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. On Fri, 4 Mar 2005 08:51:04 -0800, ?B?U2hhd24gTydEb25uZWxs?= wrote: "Larry Levinson" wrote: 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? If I had a really good sig file, this is where it would go .... |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
sometimes it takes one variable, sometimes another ...
yesterday = date - choose(weekday(date),2,3,1,1,1,1,1)
-- Regards, Tom Ogilvy "Larry Levinson" wrote in message ... 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. On Sat, 5 Mar 2005 11:35:12 -0500, "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Larry Levinson" wrote in message .. . "Bob Phillips" wrote: 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 ....) If I had a really good sig file, this is where it would go .... |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
sometimes it takes one variable, sometimes another ...
coool ... On Sat, 5 Mar 2005 16:49:01 -0500, "Tom Ogilvy"
wrote: yesterday = date - choose(weekday(date),2,3,1,1,1,1,1) -- Regards, Tom Ogilvy "Larry Levinson" wrote in message ... 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. On Sat, 5 Mar 2005 11:35:12 -0500, "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Larry Levinson" wrote in message .. . "Bob Phillips" wrote: 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 ....) If I had a really good sig file, this is where it would go .... If I had a really good sig file, this is where it would go .... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How many iterations it takes to get to 0? | Excel Worksheet Functions | |||
What it takes for Excel to run faster ? | Excel Discussion (Misc queries) | |||
TAB key takes cursor to other end of sheet... | Excel Discussion (Misc queries) | |||
Save takes seconds vs. SaveAs/Save As takes minutes | Excel Programming | |||
VBE takes two tries to hear me clearly... | Excel Programming |