Listbox and sheet updating issue
 

I need your assistance with a problem I have with a listbox.
I have a sheet containing a range "Reporting_Periods" which has col(1) = a
number 1 to 15 (Period), and Col(2) a StartDate and col(3) an EndDate.
I have a userform with one listbox, and 3 textboxes - Period, Startdate,
Enddate. The users may change the dates in an existing Period, and if the
click in the blank line under the last populated row in the listbox, they
can create a new Period.
The listbox is populated at Userform_Initialize which sets the row source
and the bound column (Period).
Everything works OK for editing existing entries and creating the first new
Period.
When the user tries to create another new Period by clicking on the blank
line under the new period just entered an error 1004 is raised indicating
that the select method failed. Here is the code I use to update the sheet:

Range("Project_Reporting_Dates_Top").Offset(ListBo x1.ListIndex, 0).Select
Selection = intPeriod
Range("Project_Reporting_Dates_Top").Offset(ListBo x1.ListIndex, 1).Select
Selection = dteSDate
Range("Project_Reporting_Dates_Top").Offset(ListBo x1.ListIndex, 2).Select
Selection = dteEdate
Selection.Value = txtEdate.Value
Call Update_ProjectInformation

At form_Initalize event I activate and unprotect the sheet. I protect the
sheet on Query_close event.

However, it seems as if something is re-protecting the sheet and it is not
in my code. If I run Debug when the error occurs and enter a sheet.unprotect
above the error line then move the run pointer to the sheet.unprotect, the
code executes.

Does anyone have an idea as to what may be causing this problem? Or, is
there a better way to update the form?

Thank you for your advice.

Rick

Listbox and sheet updating issue
 

nothing in your code protect sheet so if it happening it have to be
somewhere else. but why you select cell just to put something in it? that
what newbees do. you can just do

Range("Project_Reporting_Dates_Top").Offset(ListBo x1.ListIndex, 0).Value =
intPeriod

but if sheet protect that fail to.

"news.microsoft.com" wrote in message
...
|I need your assistance with a problem I have with a listbox.
| I have a sheet containing a range "Reporting_Periods" which has col(1) = a
| number 1 to 15 (Period), and Col(2) a StartDate and col(3) an EndDate.
| I have a userform with one listbox, and 3 textboxes - Period, Startdate,
| Enddate. The users may change the dates in an existing Period, and if the
| click in the blank line under the last populated row in the listbox, they
| can create a new Period.
| The listbox is populated at Userform_Initialize which sets the row source
| and the bound column (Period).
| Everything works OK for editing existing entries and creating the first
new
| Period.
| When the user tries to create another new Period by clicking on the blank
| line under the new period just entered an error 1004 is raised indicating
| that the select method failed. Here is the code I use to update the sheet:
|
| Range("Project_Reporting_Dates_Top").Offset(ListBo x1.ListIndex, 0).Select
| Selection = intPeriod
| Range("Project_Reporting_Dates_Top").Offset(ListBo x1.ListIndex, 1).Select
| Selection = dteSDate
| Range("Project_Reporting_Dates_Top").Offset(ListBo x1.ListIndex, 2).Select
| Selection = dteEdate
| Selection.Value = txtEdate.Value
| Call Update_ProjectInformation
|
| At form_Initalize event I activate and unprotect the sheet. I protect the
| sheet on Query_close event.
|
| However, it seems as if something is re-protecting the sheet and it is not
| in my code. If I run Debug when the error occurs and enter a
sheet.unprotect
| above the error line then move the run pointer to the sheet.unprotect, the
| code executes.
|
| Does anyone have an idea as to what may be causing this problem? Or, is
| there a better way to update the form?
|
| Thank you for your advice.
|
| Rick
|

Listbox and sheet updating issue
 

Thank you Homey. Your suggested change worked.
It wasn't a sheet protection problem after all - just a cell selection
problem.

"Homey" <none wrote in message
...
nothing in your code protect sheet so if it happening it have to be
somewhere else. but why you select cell just to put something in it?
that
what newbees do. you can just do

Range("Project_Reporting_Dates_Top").Offset(ListBo x1.ListIndex, 0).Value =
intPeriod

but if sheet protect that fail to.

"news.microsoft.com" wrote in message
...
|I need your assistance with a problem I have with a listbox.
| I have a sheet containing a range "Reporting_Periods" which has col(1) =
a
| number 1 to 15 (Period), and Col(2) a StartDate and col(3) an EndDate.
| I have a userform with one listbox, and 3 textboxes - Period, Startdate,
| Enddate. The users may change the dates in an existing Period, and if
the
| click in the blank line under the last populated row in the listbox,
they
| can create a new Period.
| The listbox is populated at Userform_Initialize which sets the row
source
| and the bound column (Period).
| Everything works OK for editing existing entries and creating the first
new
| Period.
| When the user tries to create another new Period by clicking on the
blank
| line under the new period just entered an error 1004 is raised
indicating
| that the select method failed. Here is the code I use to update the
sheet:
|
| Range("Project_Reporting_Dates_Top").Offset(ListBo x1.ListIndex,
0).Select
| Selection = intPeriod
| Range("Project_Reporting_Dates_Top").Offset(ListBo x1.ListIndex,
1).Select
| Selection = dteSDate
| Range("Project_Reporting_Dates_Top").Offset(ListBo x1.ListIndex,
2).Select
| Selection = dteEdate
| Selection.Value = txtEdate.Value
| Call Update_ProjectInformation
|
| At form_Initalize event I activate and unprotect the sheet. I protect
the
| sheet on Query_close event.
|
| However, it seems as if something is re-protecting the sheet and it is
not
| in my code. If I run Debug when the error occurs and enter a
sheet.unprotect
| above the error line then move the run pointer to the sheet.unprotect,
the
| code executes.
|
| Does anyone have an idea as to what may be causing this problem? Or, is
| there a better way to update the form?
|
| Thank you for your advice.
|
| Rick
|