Close drop box
WIN XP Pro MSOffice 2003
I am using the following code to auto drop my dropbox on entering the cell. It works well to drop the box, but then after the user makes a choice, the box will not close and allow us to move to the next cell. Could someone tell me what is missing here? BTW, kudos to GTVT06, who gave me this code in answer to my earlier SOS on this forum. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveCell.Column = 8 Then ActiveCell.Offset(1, -6).Select End If On Error GoTo Endw If ActiveCell.Validation.Type = 3 Then With Selection SendKeys "%{down}" End With End If Endw: End Sub Thank you Joanne |
Close drop box
On Jan 12, 1:11*pm, Joanne wrote:
WIN XP Pro MSOffice 2003 I am using the following code to auto drop my dropbox on entering the cell. It works well to drop the box, but then after the user makes a choice, the box will not close and allow us to move to the next cell. Could someone tell me what is missing here? BTW, kudos to GTVT06, who gave me this code in answer to my earlier SOS on this forum. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveCell.Column = 8 Then ActiveCell.Offset(1, -6).Select End If On Error GoTo Endw If ActiveCell.Validation.Type = 3 Then * *With Selection * *SendKeys "%{down}" * End With * End If Endw: End Sub Thank you Joanne Hey Joanne, me again. =D I think the adjustment would actually have to be made on the other part of your code. adjust your Worksheet_Change code to this and see if that works: Private Sub Worksheet_Change(ByVal Target As Range) With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With 'if any cell in range C16 - C65 is 0 then make D 65 Dim cell As Range Dim acd As Variant acd = ActiveCell.Address For Each cell In Sheets("lists").Range("C15:C65") If cell.Value 0 Then cell.Offset(0, 1).Select If Selection.Value = "" Then Selection.Value = 65 Else: If Selection.Value = 65 Then GoTo nxcell End If End If nxcell: Next cell On Error Resume Next Range(acd).Offset(0, 0).Select On Error Resume Next Dim ws As Worksheet Dim i As Integer Set ws = Worksheets("Lists") If Target.Column = 1 And Target.Row 1 Then If Application.WorksheetFunction.CountIf(ws.Range("Jo bDescription"), Target.Value) Then Exit Sub Else: i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1 ws.Range("A" & i).Value = Target.Value ws.Range("JobDescription").Sort Key1:=ws.Range("A1"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End If End If On Error GoTo 0 With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With End Sub |
Close drop box
Hi again - glad to hear from you again
I have stripped most of the frills from my code until I get what I consider the 'necessaries' working properly. But I just cannot get a handle on the sequence of events in Excel VBA. In my first proc, I go to the first user input box on my form when the ws first becomes active - it works just fine. But after leaving this cell, I want to goto cell B15. My syntax is: Range("B15").Select problem is I cannot figure out where to put this line of code to make it work as I expect it to. It is a user input cell, after input I want to tab to the next cell. Currently, I have this line as the first line of code in my ws_change event. It does put me in the cell, but after I do the data input, I have to tab 2 times to leave the cell. I have tried placing this line all over the place, but cannot get it to do as expected, which is become active immediately after leaving E10, allow user input, then tab to the next cell. I don't have the line in 2 places in my procedures, so why is it needing 2 tabbings to leave to the next cell? I suspect that my problem is because of this line acd = ActiveCell.Address but I'm not positive about this. If this line is causing my problem, then where do I put the B15.select line? My of my, I am so confused!! Can you see why this is a problem? If you do, could you explain it to me to help me get an idea of how the sequence of events works in ws_change? Also, if I put the line in my ws selection_change event, then I cannot get out of the cell at all - what is that about?? Option Explicit Public Sub Worksheet_Activate() Range("E10").Activate End Sub Public Sub ClearShts() Sheet3.UsedRange.Clear Sheet1.Rows("15:65").SpecialCells(xlCellTypeConsta nts).ClearContents Sheet1.Range("E10").Value = "" End Sub Private Sub Worksheet_Change(ByVal Target As Range) Range("B15").Select Dim cell As Range Dim acd As Variant acd = ActiveCell.Address For Each cell In Sheets("KelloggInvoice").Range("C15:C65") If cell.Value 0 Then cell.Offset(0, 1).Select If Selection.Value = "" Then Selection.Value = 65 Else: If Selection.Value = 65 Then GoTo nxcell End If End If nxcell: Next cell Range(acd).Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveCell.Column = 8 Then ActiveCell.Offset(1, -7).Select ActiveCell.Offset(0, 1).Select End If On Error GoTo Endw Endw: End Sub Thanks Joanne |
Close drop box
On 12 Jan., 22:15, Joanne wrote:
Hi again - glad to hear from you again I have stripped most of the frills from my code until I get what I consider the 'necessaries' working properly. But I just cannot get a handle on the sequence of events in Excel VBA. In my first proc, I go to the first user input box on my form when the ws first becomes active - it works just fine. But after leaving this cell, I want to goto cell B15. My syntax is: * * * * * * *Range("B15").Select * problem is I cannot figure out where to put this line of code to make it work as I expect it to. It is a user input cell, after input I want to tab to the next cell. Currently, I have this line as the first line of code in my ws_change event. It does put me in the cell, but after I do the data input, I have to tab 2 times to leave the cell. I have tried placing this line all over the place, but cannot get it to do as expected, which is become active immediately after leaving E10, allow user input, then tab to the next cell. I don't have the line in 2 places in my procedures, so why is it needing 2 tabbings to leave to the next cell? I suspect that my problem is because of this line * * * * acd = ActiveCell.Address but I'm not positive about this. If this line is causing my problem, then where do I put the B15.select line? *My of my, I am so confused!! Can you see why this is a problem? If you do, could you explain it to me to help me get an idea of how the sequence of events works in ws_change? Also, if I put the line in my ws selection_change event, then I cannot get out of the cell at all - what is that about?? Option Explicit Public Sub Worksheet_Activate() * Range("E10").Activate End Sub Public Sub ClearShts() * *Sheet3.UsedRange.Clear * *Sheet1.Rows("15:65").SpecialCells(xlCellTypeConst ants).ClearContents * *Sheet1.Range("E10").Value = "" End Sub Private Sub Worksheet_Change(ByVal Target As Range) Range("B15").Select Dim cell As Range Dim acd As Variant acd = ActiveCell.Address * * For Each cell In Sheets("KelloggInvoice").Range("C15:C65") * * If cell.Value 0 Then * * cell.Offset(0, 1).Select * * If Selection.Value = "" Then * * Selection.Value = 65 * * Else: If Selection.Value = 65 Then GoTo nxcell * * End If * * End If nxcell: * * Next cell * * Range(acd).Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveCell.Column = 8 Then ActiveCell.Offset(1, -7).Select ActiveCell.Offset(0, 1).Select End If On Error GoTo Endw Endw: End Sub Thanks Joanne Hi Joanne If I understand your wishes, this is what you need. Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("E10") Then Range("B15").Select 'Dim cell As Range Dim acd As Variant acd = ActiveCell.Address For Each cell In Sheets("KelloggInvoice").Range("C15:C65") If cell.Value 0 Then cell.Offset(0, 1).Select If Selection.Value = "" Then Selection.Value = 65 Else: If Selection.Value = 65 Then GoTo nxcell End If End If nxcell: Next cell Range(acd).Select End Sub Regards, Per |
Close drop box
acd=ActiveCell.Address only assigns the cell address to a variable. There is
something else that causes the tab not to take effect on the first depression. Without doing a detailed analysis, I would surmize that the cell where the cursor resides has somehow lost focus or your tab setting is out of whack. "Joanne" wrote: Hi again - glad to hear from you again I have stripped most of the frills from my code until I get what I consider the 'necessaries' working properly. But I just cannot get a handle on the sequence of events in Excel VBA. In my first proc, I go to the first user input box on my form when the ws first becomes active - it works just fine. But after leaving this cell, I want to goto cell B15. My syntax is: Range("B15").Select problem is I cannot figure out where to put this line of code to make it work as I expect it to. It is a user input cell, after input I want to tab to the next cell. Currently, I have this line as the first line of code in my ws_change event. It does put me in the cell, but after I do the data input, I have to tab 2 times to leave the cell. I have tried placing this line all over the place, but cannot get it to do as expected, which is become active immediately after leaving E10, allow user input, then tab to the next cell. I don't have the line in 2 places in my procedures, so why is it needing 2 tabbings to leave to the next cell? I suspect that my problem is because of this line acd = ActiveCell.Address but I'm not positive about this. If this line is causing my problem, then where do I put the B15.select line? My of my, I am so confused!! Can you see why this is a problem? If you do, could you explain it to me to help me get an idea of how the sequence of events works in ws_change? Also, if I put the line in my ws selection_change event, then I cannot get out of the cell at all - what is that about?? Option Explicit Public Sub Worksheet_Activate() Range("E10").Activate End Sub Public Sub ClearShts() Sheet3.UsedRange.Clear Sheet1.Rows("15:65").SpecialCells(xlCellTypeConsta nts).ClearContents Sheet1.Range("E10").Value = "" End Sub Private Sub Worksheet_Change(ByVal Target As Range) Range("B15").Select Dim cell As Range Dim acd As Variant acd = ActiveCell.Address For Each cell In Sheets("KelloggInvoice").Range("C15:C65") If cell.Value 0 Then cell.Offset(0, 1).Select If Selection.Value = "" Then Selection.Value = 65 Else: If Selection.Value = 65 Then GoTo nxcell End If End If nxcell: Next cell Range(acd).Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveCell.Column = 8 Then ActiveCell.Offset(1, -7).Select ActiveCell.Offset(0, 1).Select End If On Error GoTo Endw Endw: End Sub Thanks Joanne |
Close drop box
Where do I access tab settings, and where does got focus/lost focus
live? Thanks Joanne JLGWhiz wrote: acd=ActiveCell.Address only assigns the cell address to a variable. There is something else that causes the tab not to take effect on the first depression. Without doing a detailed analysis, I would surmize that the cell where the cursor resides has somehow lost focus or your tab setting is out of whack. "Joanne" wrote: Hi again - glad to hear from you again I have stripped most of the frills from my code until I get what I consider the 'necessaries' working properly. But I just cannot get a handle on the sequence of events in Excel VBA. In my first proc, I go to the first user input box on my form when the ws first becomes active - it works just fine. But after leaving this cell, I want to goto cell B15. My syntax is: Range("B15").Select problem is I cannot figure out where to put this line of code to make it work as I expect it to. It is a user input cell, after input I want to tab to the next cell. Currently, I have this line as the first line of code in my ws_change event. It does put me in the cell, but after I do the data input, I have to tab 2 times to leave the cell. I have tried placing this line all over the place, but cannot get it to do as expected, which is become active immediately after leaving E10, allow user input, then tab to the next cell. I don't have the line in 2 places in my procedures, so why is it needing 2 tabbings to leave to the next cell? I suspect that my problem is because of this line acd = ActiveCell.Address but I'm not positive about this. If this line is causing my problem, then where do I put the B15.select line? My of my, I am so confused!! Can you see why this is a problem? If you do, could you explain it to me to help me get an idea of how the sequence of events works in ws_change? Also, if I put the line in my ws selection_change event, then I cannot get out of the cell at all - what is that about?? Option Explicit Public Sub Worksheet_Activate() Range("E10").Activate End Sub Public Sub ClearShts() Sheet3.UsedRange.Clear Sheet1.Rows("15:65").SpecialCells(xlCellTypeConsta nts).ClearContents Sheet1.Range("E10").Value = "" End Sub Private Sub Worksheet_Change(ByVal Target As Range) Range("B15").Select Dim cell As Range Dim acd As Variant acd = ActiveCell.Address For Each cell In Sheets("KelloggInvoice").Range("C15:C65") If cell.Value 0 Then cell.Offset(0, 1).Select If Selection.Value = "" Then Selection.Value = 65 Else: If Selection.Value = 65 Then GoTo nxcell End If End If nxcell: Next cell Range(acd).Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveCell.Column = 8 Then ActiveCell.Offset(1, -7).Select ActiveCell.Offset(0, 1).Select End If On Error GoTo Endw Endw: End Sub Thanks Joanne |
All times are GMT +1. The time now is 02:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com