| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 ComboBox Code Change event
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.x ls!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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2000 ComboBox Code Change event
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 "Matt." wrote in message .. . 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.x ls!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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2000 ComboBox Code Change event
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 "Matt." wrote in message .. . 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.x ls!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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2000 ComboBox Code Change event
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. -----Original Message----- 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 "Matt." wrote in message . .. 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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| combobox code | Excel Discussion (Misc queries) | |||
| ComboBox Code | New Users to Excel | |||
| ComboBox code | Excel Worksheet Functions | |||
| Combobox Click event triggered when copying worksheet | Excel Programming | |||
| Excel 2000 VBA Form Combobox value | Excel Programming | |||
Linear Mode
