| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dropdown List
Thank you Gord, you are a legend.
Cheers Lynda "Gord Dibben" wrote: Sounds like maybe you have sheetactivate code in sheet2 that tries to run a macro when the sheet2 is activated(selected) The sheetactivate code could also be in Thisworkbook module as Private Sub Workbook_SheetActivate(ByVal Sh As Object) code here End Sub If you can't find anything, send the workbook to my email at gorddibbATshawDOTca make the appropriate changes to address. Gord On Tue, 27 Oct 2009 03:14:01 -0700, Lynda wrote: Gord, I am so sorry for the delay in getting back to you, got sidetracked with some other urgent stuff at work. Anyway I cant get the code to work. I have followed your instructions. I went to sheet 2 (two) and named the destination cell mycell without the quotation marks. I then clicked on the tab to the sheet I have my dropdown in (which is called Bulk Recruitment Placements) I right clicked and went to view code. In the code sheet I copied the code you wrote for me, Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo stoppit Application.EnableEvents = False If Target.Address = "$G$28" And Target.Value 1 Then Application.Goto Reference:="mycell" End If stoppit: Application.EnableEvents = True End Sub All I changed was the $A$1 to $G$28 which is where my dropdown ended up. The dropdown is linked to this cell. I keep getting the following error message - The macro Bulk Recruitment Placements.xls!DropDown36_Change cannot be found. I have other code on the same page as it also needs to be there for it to operate other dropdowns as well. I have tried removing that code in case it was interfering with your code and it doesnt make any difference. I have tried lots of different things but nothing seems to work. I just get really annoyed with myself that I cant do this stuff myself. Thank goodness for you people. Lynda "Gord Dibben" wrote: What is "trouble getting it to work"? You have named the cell on sheet2 as "mycell"..........no quotes when naming but you must use the quotes in the code as shown. Does "mycell" refer to =Sheet2!cellref where cellref is your selected cell? Did you paste the code into sheet1 module per instructions? Is your dropdown in A1 on sheet1? The code will work on 2007 and 2003. Did you save the workbook as macro-enabled *.xlsm? Gord On Sat, 24 Oct 2009 05:52:01 -0700, Lynda wrote: Thank you Gord. Yes I did mean for the code to send it to sheet 2. What I have with the drop down is that if the client wants to choose more than one item then they will be sent to sheet 2 where there are more boxes for them to enter their requests. then once they have comleted their requests they hit a hyperlink which will send them back to sheet1. I am having trouble getting your code to work. Will the fact that I am using Excel 2007 at home be the problem, its just that we use 2003 at work which is where I need to use the form Im creating. "Gord Dibben" wrote: A formula can only return results. It can't take you anywhere. If you want to go to.....select......another cell you must use VBA First we name the cell to go to. Select the cell on Sheet2 and InsertNameDefine Name it mycell Right-click on Sheet1 tab and "View Code" Copy/paste this code into that sheet module. Edit to suit then Alt + q to go back to Excel. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo stoppit Application.EnableEvents = False If Target.Address = "$A$1" And Target.Value 1 Then Application.Goto Reference:="mycell" End If stoppit: Application.EnableEvents = True End Sub Pick a number greater than 1 from the DV dropdown list. Gord Dibben MS Excel MVP On Fri, 23 Oct 2009 15:49:01 -0700, Lynda wrote: Sorry Folks but whatever has happened it is just not working. Thank you for your help. Cheers Lynda "Lynda" wrote: I have a dropdown list on page 1, with numbers from 1 to 10. I have the dropdown linked to the cell underneath (eg A1). If the number in the dropdown (and cell A1) is greater than 1, I want it to go to a cell in sheet 2. I have tried the IF statement but I am obviously doing something wrong. Your help would be greatly appreciated. Lynda . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| dropdown list determined by another dropdown list | Excel Discussion (Misc queries) | |||
| Excell Dropdown List. Display alternate text than found in list. | Excel Discussion (Misc queries) | |||
| Dropdown List - list item endings not visible if column too narrow | Excel Discussion (Misc queries) | |||
| result of selecting from the dropdown list should be a dropdown list | Excel Worksheet Functions | |||
| result of selecting from the dropdown list should be a dropdown list | Excel Worksheet Functions | |||
Hybrid Mode
