Excel 2000 ComboBox Code Change event

  • Thread starter Thread starter Matt.
  • Start date Start date
M

Matt.

Hi all!

I have a Form ComboBox called "Drop Down 15" on a worksheet called "Daily"
in a workbook called "DailyMaintenancePerformace". The source for the list
is on a worksheet called "Log". The source values are date values, which is
why I'm using a Form object and not the Toolbox object.

I would like to have the user choose a date, and then have various cell
values on the Daily sheet populated by cell values from the corresponding
date from the Log sheet. The code I'm using is attached at the end of this
note.

The error I'm getting is 424 - Object Required. I'm receiving the error on
the MsgBox line. I've done some online searching, but nothing is being
returned that I think is useful.

Any assistance greatly appreciated.

cheers,
Matt.


Sub DropDown15_Change()
MsgBox DailyMaintenancePerformance.xls!("Drop Down 15").Value

Sheets("Log").Select
Selection.Find(What:=DailyMaintenancePerformance.xls!DropDown15.Value, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).Activate
iRow = ActiveCell.Row
iCol = ActiveCell.Column
Sheets("Daily").G9.Value = Cells(iRow, iCol + 1).Value
Sheets("Daily").G10.Value = Cells(iRow, iCol + 2).Value
Sheets("Daily").G11.Value = Cells(iRow, iCol + 3).Value
Sheets("Daily").G12.Value = Cells(iRow, iCol + 4).Value
Sheets("Daily").G15.Value = Cells(iRow, iCol + 5).Value
Sheets("Daily").G16.Value = Cells(iRow, iCol + 6).Value

Sheets("Daily").Select

End Sub
 
Hi all!

I've made some progress. Surprisingly. I stole some code from a 2001 post
by Tom Ogilvy, and it has erased the Object Required error. I have no idea
what Application.Caller is, but it seems to have done the trick. So thanks
Tom.

The problem I'm having now is the value being returned seems to be the Index
value, not the date value that the user is selecting. I think I can work
around that by using the Index value to find the correct row instead of
using the Find statement, but, I was wondering if there was a way to get the
Date value.

cheers,
Matt.


Sub DropDown15_Change()
Dim cBox As DropDown
Dim sName As String

sName = Application.Caller
Set cBox = ActiveSheet.DropDowns(sName)

'MsgBox DailyMaintenancePerformance.xls!("Drop Down 15").Value
MsgBox cBox.Value

Sheets("Log").Select
Selection.Find(What:=cBox.Value, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).Activate
iRow = ActiveCell.Row
iCol = ActiveCell.Column
Sheets("Daily").G9.Value = Cells(iRow, iCol + 1).Value
Sheets("Daily").G10.Value = Cells(iRow, iCol + 2).Value
Sheets("Daily").G11.Value = Cells(iRow, iCol + 3).Value
Sheets("Daily").G12.Value = Cells(iRow, iCol + 4).Value
Sheets("Daily").G15.Value = Cells(iRow, iCol + 5).Value
Sheets("Daily").G16.Value = Cells(iRow, iCol + 6).Value

Sheets("Daily").Select

End Sub
 
Hi all!

I've made it work. Thanks to any who spent time on this.

cheers,
Matt.

Sub DropDown15_Change()
Dim cBox As DropDown
Dim sName As String

sName = Application.Caller
Set cBox = ActiveSheet.DropDowns(sName)

MsgBox cBox.Value

Sheets("Log").Select
Cells(1, 1).Select

ActiveCell.Cells(cBox.Value + 1, 1).Select

iRow = ActiveCell.Row
iCol = ActiveCell.Column

MsgBox Cells(iRow, iCol + 1).Value
Sheets("Daily").Cells(9, 7).Value = Cells(iRow, iCol + 1).Value
Sheets("Daily").Cells(10, 7).Value = Cells(iRow, iCol + 2).Value
Sheets("Daily").Cells(11, 7).Value = Cells(iRow, iCol + 3).Value
Sheets("Daily").Cells(12, 7).Value = Cells(iRow, iCol + 4).Value
Sheets("Daily").Cells(15, 7).Value = Cells(iRow, iCol + 5).Value
Sheets("Daily").Cells(16, 7).Value = Cells(iRow, iCol + 6).Value

Sheets("Daily").Select

End Sub
 
Use:
Application.Range(cBox.ListFillRange).Cells
(cBox.Value).Value

An alternative to using VBA code here is to link the
dropdown value to a cell, then use that value in a lookup
formula to get the values to the Daily sheet.

Cheers,
Dave.
 
Back
Top