HTH,
The TimeSheet is Protected but only cells that are always fixed values.
D2 is an unprotected cell.
Corey....
"cush" <(E-Mail Removed)> wrote in message
news:9B56D3BA-0036-4E7A-81D4-(E-Mail Removed)...
> Corey,
>
> When you try to enter new data in Sheets("TimeSheet).Range("D2")
> by any chance is this sheet still protected? ( I noticed that you are
> first
> unprotecting the Data Sheet but not the TimeSheet.)
>
>
> ' Repopulate TimeSheet from Data Sheet
> Call UnprotectData 'Unprotects Data Sheet
> With Sheets("TimeSheet")
> .Select
> ' MsgBox "error 3" ' <= = If i removed the Hyphen on this line I Do Get
> the
> "error 3" PopUp to confirm this line of code Should Execute
> .Range("D2").Text = rngFound.Text '
>
> You might also try commenting out the On Error Resume Next and then
> execute
> the code line by line to see what the error is when it reaches the above
> line.
>
> HTH
>
> "Corey ...." wrote:
>
>> The below code finds a value in Sheets("Data") selected from a
>> Listbox(Listbox2)on a Userform7
>>
>> If the value (Offset(0,1)) in Sheets("Data") is ALSO the value selected
>> in
>> ListBox1 on the same Userform(Userform7), then the required values are
>> placed from Sheets("Data") into the Sheet("TimeSheet").
>> The data in Sheets("Data") is basically stored values from a TimeSheet.
>> Each TimeSheet is Stored on a Single Row in Sheets("Data").
>>
>> Then if the user wants to View an existing TimeSheet, the
>> Userform(Userform7) shows 2(two) Listboxes(Listbox1) & (Listbox2)
>> When Userform7 Initializes, ListBox1 is Populated with ALL Values
>> (Removes
>> Duplicates) in Column A(Column A = The Pay Week Ending date{dd/mm/yy}) of
>> the Data Sheet.
>> If a Value in Listbox1 is clicked, all Values that have the Listbox1
>> value
>> in Column A and Have a Value in Column B also, these are then populated
>> into
>> ListBox2.
>>
>> When the user then Clicks on a value in Listbox2, the following code
>> executes.
>> Which(as in other workbooks and similar code) places the required values
>> from the Data sheet(from a Single Row) into the TimeSheet(A TimeSheet
>> Layout
>> sheet).
>> The user can then View or Print the TimeSheet.
>>
>> Problem is, the Highlighted lines of code are NOT bringing the values
>> back
>> into the TimeSheet fromt he Data sheet for some reason, WHY ?
>> I thought it may have been because i dropped off the [.Value] at the end
>> of
>> each[.Range("X#")] and [.Offset(#,#)], but it made no difference either.
>> I have saved and reebooted Excel and PC, to no avail either.
>>
>> Excel calculations are set to AUTO(if that made any differrence).
>>
>> ~~~~~~~~~~~~~~~~~~~~~~~
>> Private Sub ListBox2_Click()
>> Application.ScreenUpdating = False
>> ' Repopulate TimeSheet with Times from Data Sheet
>> With Workbooks("TimeSheets")
>> Dim rngFound As Range
>> On Error Resume Next
>> With Worksheets("Data").Range("A:A")
>> Set rngFound = .Find(What:=Me.ListBox2.Text, After:=.Cells(1),
>> LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
>> SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False)
>> If Not rngFound Is Nothing Then
>> If rngFound.Offset(0, 1).Value = Me.ListBox1.Value Then
>> MsgBox "error 1" ' Variable that should not be able to be executed
>> anyhow
>> due to only possible options were selected from the Userform7
>> Else
>> End If
>> MsgBox "error 2" ' Variable that should not be able to be executed
>> either
>> Else
>> ' Repopulate TimeSheet from Data Sheet
>> Call UnprotectData 'Unprotects Data Sheet
>> With Sheets("TimeSheet")
>> .Select
>> ' MsgBox "error 3" ' <= = If i removed the Hyphen on this line I Do Get
>> the
>> "error 3" PopUp to confirm this line of code Should Execute
>> .Range("D2").Text = rngFound.Text ' <= = = The lines here and below
>> are
>> NOT working. Similar codes work great in other workbooks
>> .Range("K2") = rngFound.Offset(0, 1) ' <= = = The lines here and below
>> are
>> NOT working. Similar codes work great in other workbooks
>> .Range("C4") = rngFound.Offset(0, 2) ' <= = = The lines here and below
>> are
>> NOT working. Similar codes work great in other workbooks
>> .Range("B5") = rngFound.Offset(0, 3) ' <= = = The lines here and below
>> are
>> NOT working. Similar codes work great in other workbooks
>> .Range("C5") = rngFound.Offset(, 4) ' <= = = The lines here and below
>> are
>> NOT working. Similar codes work great in other workbooks
>> .Range("C6") = rngFound.Offset(, 5) ' <= = = The lines here and below
>> are
>> NOT working. Similar codes work great in other workbooks
>> .Range("C7") = rngFound.Offset(, 6) ' <= = = The lines here and below
>> are
>> NOT working. Similar codes work great in other workbooks
>> .Range("C8") = rngFound.Offset(, 7) ' <= = = The lines here and below
>> are
>> NOT working. Similar codes work great in other workbooks
>> End With
>> Call ProtectData 'Protects Data Sheet
>> End If
>> End With
>> End With
>> Unload Me
>> Application.ScreenUpdating = True
>> End Sub
>> ~~~~~~~~~~~~~~~~~~~~~~~~
>>
>>
>> What could it be?
>>
>>
>> Corey....
>>
>>
>>
|
Similar Threads
