How to have auto dialog box showing when selecting cell

[rob nobel]

I would like to have a dialog box appear each time a cell is selected in a
column.
I need the dialog box show the next cheque number and allow the user to
change that if necessary.
The cells cannot have a formula in it as that would cause problems when
sorting.
Is there a way to do this?
Rob

 

[Kieran]

You could use data validation to do this.

Firstly in an empty area of the worksheet define a cell that is equa
to max(column range where the cehque numbers are shown) + 1. (ie
max(a:a) + 1

Then set the data validation for the whole cheque input range to list

list = the address of the new cell.

Then set the input message (if needed),
and a message if the user does not accept the prompted next chequ
number (if needed) , ensure that the data validation error aler
message type is set to warning or information.

This should give you what you want. However please note that if yo
add a cheque number in that is 2000, and the maximum cheque numbe
before was 1890, then the user will need to keep track of the missin
110 cheque numbers as the next chque number prompted will be 2001.

The alternative is worksheet_selection_change event macro.
This would give you more control but will be harder to set up

 

[rob nobel]

Thanks for that Kieran.
I appreciate your input re the Data validation procedure and will certainly
use that if all else fails, but I'm really after a dialog box appearing as
in my first post so that if the Chq No. is correct all the user need do is
click OK.
My first post was...
I would like to have a dialog box appear each time a cell is selected in a
column. I need the dialog box show the next cheque number and allow the
user to change that if necessary. The cells cannot have a formula in it as
that would cause problems when sorting.

If someone could help me set up some VBA code for this I would be most
appreciative!
Thanks,
Rob

 

[Kieran]

Then try this one... (in the worksheet module that contains the dat
entry stuff)

Remember to change the line

Set rngChequeNo = [a1:a500]

to a more appropriate range for your worksheet.
\

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Dim rngChequeNo As Range
Dim af As WorksheetFunction

Set af = Application.WorksheetFunction
Set rngChequeNo = [a1:a500]
Dim ILastCheque As Integer
If Intersect(Target, rngChequeNo) Is Nothing Then Exit Sub

If Target.Rows.Count * Target.Cells.Count <> 1 Then
MsgBox "I can only process one cell at a time", vbInformation
"Cell selection Error"
Target.Cells(1).Select
Exit Sub
End If

ILastCheque = af.Max(rngChequeNo) + 1
Application.EnableEvents = False

Target.Value = ILastCheque
Application.EnableEvents = True

End Su

 

[rob nobel]

Hi Kieran,
Thanks for your reply. I tried to put it into my procedure but keep getting
Run-time error 6...."Overflow" occuring.
Any Ideas?

The code is as follows:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim rngChequeNo As Range
Dim af As WorksheetFunction
Set af = Application.WorksheetFunction
Set rngChequeNo = [G27:G1524]
Dim ILastCheque As Integer
If Intersect(Target, rngChequeNo) Is Nothing Then Exit Sub
If Target.Rows.Count * Target.Cells.Count <> 1 Then
MsgBox "I can only process one cell at a time", vbInformation, _
"Cell selection Error"
Target.Cells(1).Select
Exit Sub
End If

Rob

 

[Shailesh Shah]

Hi Rob,

Try this modified codes.

Private Sub Worksheet_SelectionChange(ByVal Target As
Excel.Range)

Dim rngChequeNo As Range

Dim af As WorksheetFunction

Set af = Application.WorksheetFunction

Set rngChequeNo = [G27:G1524]

Dim ILastCheque

If Target.Cells.Count > 1 Or Intersect(Target,
rngChequeNo) Is Nothing Then Exit Sub

ILastCheque = InputBox("Enter Ch. No.", "Cheque Book
Entry", af.Max(rngChequeNo) + 1)

If ILastCheque <> "" Then Target.Value = ILastCheque

End Sub

Regards,
Shailesh Shah
http://members.lycos.co.uk/shahweb/
(Excel Add-ins)

 

[rob nobel]

Hi Shailesh,
The following line brings about an error. That is, "Type mismatch"
ILastCheque = InputBox("Enter Cheque. No.,", af.Max(rngChequeNo) + 1)
But don't persist with it as I am using a different process within a formula
instead.
Thanks for the help though.
Rob

Hi Rob,

Try this modified codes.

Private Sub Worksheet_SelectionChange(ByVal Target As
Excel.Range)

Dim rngChequeNo As Range

Dim af As WorksheetFunction

Set af = Application.WorksheetFunction

Set rngChequeNo = [G27:G1524]

Dim ILastCheque

If Target.Cells.Count > 1 Or Intersect(Target,
rngChequeNo) Is Nothing Then Exit Sub

ILastCheque = InputBox("Enter Ch. No.", "Cheque Book
Entry", af.Max(rngChequeNo) + 1)

If ILastCheque <> "" Then Target.Value = ILastCheque

End Sub

Regards,
Shailesh Shah
http://members.lycos.co.uk/shahweb/
(Excel Add-ins)

-----Original Message-----
Hi Kieran,
Thanks for your reply. I tried to put it into my procedure but keep getting
Run-time error 6...."Overflow" occuring.
Any Ideas?

The code is as follows:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim rngChequeNo As Range
Dim af As WorksheetFunction
Set af = Application.WorksheetFunction
Set rngChequeNo = [G27:G1524]
Dim ILastCheque As Integer
If Intersect(Target, rngChequeNo) Is Nothing Then Exit Sub
If Target.Rows.Count * Target.Cells.Count <> 1 Then
MsgBox "I can only process one cell at a time", vbInformation, _
"Cell selection Error"
Target.Cells(1).Select
Exit Sub
End If

Rob


.