Close drop box

[Joanne]

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

 

[GTVT06]

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("JobDescription"),
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

 

[Joanne]

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(xlCellTypeConstants).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

 

[Per Jessen]

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(xlCellTypeConstants).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

 

[JLGWhiz]

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]

Where do I access tab settings, and where does got focus/lost focus
live?
Thanks
Joanne