Code does not seem to work, but it should.

C

Corey ....

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....
 
C

cush

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 .... said:
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....
Click to expand...
 
C

Corey

HTH,
The TimeSheet is Protected but only cells that are always fixed values.
D2 is an unprotected cell.

Corey....
cush said:
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 .... said:
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....
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code does not error but no copy either 4
rngFound not finding correct value when more than one match 0
Similar Book to Book Code, one works other does not 11
Keep Original Sheets Font Format when Populating data into other sheet 3
Add 2nd condition to Find 3
Is there something wrong with my code? 5
Stop TextBox Clearing Cell 3
does not work when i use 1000 1

Top