user form entry to find values then insert new data in cell.

srogers

New Member
Joined
Mar 26, 2006
Messages
9
I have a userform that enables users to input a date(DTPicker1), Shift(cboShift) and name(cboname). I can get it to enter the data in a row at a time but I need it to search through a list to find the date and shift they have entered and then add their name to the name column (which will be empty).

Could someone please point me in the right direction, I am sure it will use the Offset function but how, I am not sure.

Many Thanks

Simon
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
post the current code you have.

my analysis;
find the value of Date and Shift from a list, if record had been found then put the name into corresponding row/column from Name, if nothing is found, then insert the Date,Shift,Name into new row. is that correct?
 

srogers

New Member
Joined
Mar 26, 2006
Messages
9
Code posted, ignore the countif code, I am experimenting with that part, basically it tells me if someone has already taken the date and shift that they are entering. What I need is to find a date and shift in the columns and then enter their name in the third column.

Many thanks, really do appreciate the help.

Simon

Private Sub cmdOK_Click()
Application.Cursor = xlWait
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Book1.xls"
Range("A1").Select

If WorksheetFunction.CountIf(Range("[Book1.xls]Sheet1!shift"), cboShift.Value) > 0 Then GoTo Simon Else GoTo Simon3

Simon:

If WorksheetFunction.CountIf(Range("[Book1.xls]Sheet1!Date"), DTPicker1.Value) > 0 Then GoTo Simon1 Else GoTo Simon3

Simon1:

MsgBox DTPicker1.Value & cboShift.Value & " already used"
GoTo Finish

Simon3:
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = cboName.Value
ActiveCell.Offset(0, 1) = cboShift.Value
ActiveCell.Offset(0, 2) = DTPicker1.Value

'Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wbname As String

Finish:
Range("A1").Select
Application.Cursor = xlDefault
ActiveWorkbook.Close True
Application.ScreenUpdating = True

End Sub



Private Sub UserForm_Initialize()
With cboShift
.AddItem "Early"
.AddItem "Late"
.AddItem "Night"
End With
cboName.Value = ""
With cboName
.AddItem "Simon"
.AddItem "Wendy"
.AddItem "Micheal"
.AddItem "Pete"
.AddItem "Keith"
End With
cboName.Value = ""
End Sub
 

Forum statistics

Threads
1,136,269
Messages
5,674,743
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top