PDA

View Full Version : Solved: Accessing Cells With Activecell.offset



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

herzberg
04-01-2008, 10:59 PM
You'll need to specify what property you need from the Offset cells. In this case, I suppose you want the value, so it should look something like:

With cboGroupName
.AddItem ActiveCell.Offset(0, 3).Value
End With Do it for the rest of the parts and I think everything should work out fine.

Bob Phillips
04-02-2008, 01:17 AM
Cross-posted at OzGrid http://www.ozgrid.com/forum/showthread.php?t=87428,
and I have seen it in at least one other place.

websmythe
04-02-2008, 07:41 PM
You'll need to specify what property you need from the Offset cells. In this case, I suppose you want the value, so it should look something like:

With cboGroupName
.AddItem ActiveCell.Offset(0, 3).Value
End With Do it for the rest of the parts and I think everything should work out fine.
Thanx for the help. I tried what you said and it still seems to want to treat the zero as an 'absolute' reference to Row 1 (aka... my column names). BUT ...using the '.Value' attribute prompted me to remember the Range() function (go figure), and that solved the problem. eg:
With cboGroupName
.AddItem Range("D"+Cstr(i)).Value
End With

Thanx again, much appreciated.

websmythe
04-02-2008, 07:46 PM
Cross-posted at OzGrid... ...and I have seen it in at least one other place.
Yup. Cause I've been looking for an answer for a project that should've been finished 2 weeks ago. And I cancelled my account on OzGrid. Is there a problem with that??

lucas
04-02-2008, 08:47 PM
Yeah, it's kind of a sore spot with most of the people that you are asking for help.....so I would say you should read our faq at the top of the page and this (http://www.excelguru.ca/node/7). It's mostly courtesy and some will not help you if they know you are just posting your question in every forum you can find......

My advice would be to try sticking with one forum for a day or two and then if you don't get any help you can reasonably move on but provide the links in case someone provides a solution......

websmythe
04-02-2008, 10:25 PM
Yeah, it's kind of a sore spot with most of the people that you are asking for help...

Thanks for the heads up.

[edit]
Just in case anyone misconstrues my actions or my attitude... I don't make a personal habit of cross-posting, and I track where I have posted so that I can return and and leave a thank you reply ASAP, with the answer I found, just in case it might help others. The only time I cross-link is internally within the same forum site, as required, because I have been under the impression that cross-linking between external forum sites was in bad taste.

Aussiebear
04-03-2008, 02:58 AM
Cross Posting without showing the link is most certainly in "bad taste". We not against cross posting as such, but it shows some respect to those members at each forum where you decide to post, where you are able to wait a few days before posting the same question on another forum.

I believe that this is the point that Lucas is making.

Apart from that, welcome to this forum, there's some really great people operating here.

websmythe
04-03-2008, 08:57 AM
Thanks.