websmythe
04-01-2008, 10:09 PM
Hi. I'm new to Excel & VBA. I have a form that uses a set of dynamically filled combo boxes. In the code below, Activecell.offset(0, #) values in the For...Next loop works just fine, but when filling the combo boxes, Activecell.offset(0, #) doesn't work. Activecell.offset(i, #) doesn't work either. Obviously, I'm missing something here... :)
Any help is much appreciated.
Thanx.
Version: Excel/VBA 2003
Goal: Scan each row in the worksheet for specified username and TimeOut. value = "" and retrieve the all relevant cell data
Sample Data: See attached worksheet
Problem: Accessing the cells using Activecell.offset()
Private Sub cboUserName_change()
'Define vars
Dim usrnam As String
Dim found As Boolean
Dim i As Integer
Dim intRowCount As Integer
'fill vars
usrnam = cboUserName.Value
found = False
'trap for blank username
If usrnam <> "" Then
'select worksheet
ActiveWorkbook.Sheets("Data").Activate
Range("A1").Select
intRowCount = Range("A1").CurrentRegion.Rows.Count - 1
'search rows for existing record
For i = 1 To intRowCount
'ActiveCell.Offset(i, 0).Select doesn't work
ActiveCell.Offset(1, 0).Select
'Check for existing record
'if record:username = current combobox:username
'and TimeOut is blank
If ActiveCell.Offset(0, 2) = usrnam _
And ActiveCell.Offset(0, 9) = "" Then 'This works
found = True
'newrec is Public and called by cmdOk_Click()
newrec = False
Exit For
End If
Next
End If
'PROCESS EXISTING RECORD
'Fill comboboxes on form with cell/column data from current row
If found = True Then
'RESET COMBOBOX VALUESTO SINGLE VALUES
'except cboUserName
cboGroupName.Clear
With cboGroupName
'ActiveCell.Offset(i, 3) doesn't work
.AddItem ActiveCell.Offset(0, 3)
End With
cboGroupName.ListIndex = 0
cboJobName.Clear
With cboJobName
'ActiveCell.Offset(i, 4) doesn't work
.AddItem ActiveCell.Offset(0, 4)
End With
cboJobName.ListIndex = 0
cboTaskItem.Clear
With cboTaskItem
'ActiveCell.Offset(i, 5) doesn't work
.AddItem ActiveCell.Offset(0, 5)
End With
cboTaskItem.ListIndex = 0
cboTaskName.Clear
With cboTaskName
'ActiveCell.Offset(i, 6) doesn't work
.AddItem ActiveCell.Offset(0, 6)
End With
cboTaskName.ListIndex = 0
cboTaskDetail.Clear
With cboTaskDetail
'ActiveCell.Offset(i, 7) doesn't work
.AddItem ActiveCell.Offset(0, 7)
End With
cboTaskDetail.ListIndex = 0
txtTimeIn.Value = ""
'ActiveCell.Offset(i, 8) doesn't work
txtTimeIn.Value = Format(ActiveCell.Offset(0, 8), "m/d/yy hh:mm am/pm")
Beep
txtErrorMsg.Caption = "Existing Record found! Click TimeOut, then Click Ok"
cmdTimeOut.SetFocus
Else
'SETUP COMBOBOXES FOR NEW RECORD
'newrec is Public and called by cmdOk_Click()
'to validate exisiting and new records,
'both of which are dependant on txtTimeOut.value
newrec = True
'RESET COMBOBOX VALUES TO MULTIPLE VALUES
'Clear all Combo boxes except user name
'cboUserName.Clear
cboGroupName.Clear
cboJobName.Clear
cboTaskItem.Clear
cboTaskName.Clear
cboTaskDetail.Clear
' Sert time values
txtTimeIn.Value = Format(Now, "m/d/yy hh:mm am/pm")
txtTimeOut.Value = ""
txtTotalTime.Value = ""
'Preset focus to Group Names
FillComboGroupnames (usrnam)
cboGroupName.SetFocus
txtErrorMsg.Caption = "Create New Record, then Click Ok"
End If
End Sub
Any help is much appreciated.
Thanx.
Version: Excel/VBA 2003
Goal: Scan each row in the worksheet for specified username and TimeOut. value = "" and retrieve the all relevant cell data
Sample Data: See attached worksheet
Problem: Accessing the cells using Activecell.offset()
Private Sub cboUserName_change()
'Define vars
Dim usrnam As String
Dim found As Boolean
Dim i As Integer
Dim intRowCount As Integer
'fill vars
usrnam = cboUserName.Value
found = False
'trap for blank username
If usrnam <> "" Then
'select worksheet
ActiveWorkbook.Sheets("Data").Activate
Range("A1").Select
intRowCount = Range("A1").CurrentRegion.Rows.Count - 1
'search rows for existing record
For i = 1 To intRowCount
'ActiveCell.Offset(i, 0).Select doesn't work
ActiveCell.Offset(1, 0).Select
'Check for existing record
'if record:username = current combobox:username
'and TimeOut is blank
If ActiveCell.Offset(0, 2) = usrnam _
And ActiveCell.Offset(0, 9) = "" Then 'This works
found = True
'newrec is Public and called by cmdOk_Click()
newrec = False
Exit For
End If
Next
End If
'PROCESS EXISTING RECORD
'Fill comboboxes on form with cell/column data from current row
If found = True Then
'RESET COMBOBOX VALUESTO SINGLE VALUES
'except cboUserName
cboGroupName.Clear
With cboGroupName
'ActiveCell.Offset(i, 3) doesn't work
.AddItem ActiveCell.Offset(0, 3)
End With
cboGroupName.ListIndex = 0
cboJobName.Clear
With cboJobName
'ActiveCell.Offset(i, 4) doesn't work
.AddItem ActiveCell.Offset(0, 4)
End With
cboJobName.ListIndex = 0
cboTaskItem.Clear
With cboTaskItem
'ActiveCell.Offset(i, 5) doesn't work
.AddItem ActiveCell.Offset(0, 5)
End With
cboTaskItem.ListIndex = 0
cboTaskName.Clear
With cboTaskName
'ActiveCell.Offset(i, 6) doesn't work
.AddItem ActiveCell.Offset(0, 6)
End With
cboTaskName.ListIndex = 0
cboTaskDetail.Clear
With cboTaskDetail
'ActiveCell.Offset(i, 7) doesn't work
.AddItem ActiveCell.Offset(0, 7)
End With
cboTaskDetail.ListIndex = 0
txtTimeIn.Value = ""
'ActiveCell.Offset(i, 8) doesn't work
txtTimeIn.Value = Format(ActiveCell.Offset(0, 8), "m/d/yy hh:mm am/pm")
Beep
txtErrorMsg.Caption = "Existing Record found! Click TimeOut, then Click Ok"
cmdTimeOut.SetFocus
Else
'SETUP COMBOBOXES FOR NEW RECORD
'newrec is Public and called by cmdOk_Click()
'to validate exisiting and new records,
'both of which are dependant on txtTimeOut.value
newrec = True
'RESET COMBOBOX VALUES TO MULTIPLE VALUES
'Clear all Combo boxes except user name
'cboUserName.Clear
cboGroupName.Clear
cboJobName.Clear
cboTaskItem.Clear
cboTaskName.Clear
cboTaskDetail.Clear
' Sert time values
txtTimeIn.Value = Format(Now, "m/d/yy hh:mm am/pm")
txtTimeOut.Value = ""
txtTotalTime.Value = ""
'Preset focus to Group Names
FillComboGroupnames (usrnam)
cboGroupName.SetFocus
txtErrorMsg.Caption = "Create New Record, then Click Ok"
End If
End Sub