cell value change (1 Viewer)

[santoshdream]

Hi All,

My moto is to whenever i change from A1 to A20 cell value. If my cell value is more than 25000.it should display the msg box saying " Please enter the value less than 25000.

Note:- I have tried conditional formatting but it failes when i am copying any value from other sheet to my sheet its not working. It will paste any value more than 25000 without any error.

I would really appreciate if you could give me any sample excel file with vba code thanks a lot.

Please help urgent..


Thanks and Regards

Sunny

 

[santoshdream]

Hi All,

My moto is to whenever i change from A1 to A20 cell value. If my cell value is more than 25000.it should display the msg box saying " Please enter the value less than 25000.

Note:- I have tried conditional formatting but it failes when i am copying any value from other sheet to my sheet its not working. It will paste any value more than 25000 without any error.

I would really appreciate if you could give me any sample excel file with vba code thanks a lot.

Please help urgent..


Thanks and Regards

Sunny

 

[Dennisk]

you may be better off posting this in the Excel forum and not the Access forum.

 

[namliam]

Add some code to your spreadsheet... Use the change event of the sheet, something like so:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 10 Then MsgBox Target
End Sub

I hope you get the idea, if you have questions, post back.

 

[Vader]

I hope this will help you!!!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = ActiveSheet.Range("a1:a20")
If Not Intersect(Target, rng) Is Nothing And Target.Cells.Value > 25000 Then
MsgBox "Please enter the value less than 25000!"
End If
End Sub

 

[santoshdream]

I am still confused


I would really appreciate if you could give me any sample excel file with vba code or full code for the same.



Thanks and regards

 

[santoshdream]

Hi,

Thanks for yor effort.

I am still confused.

Please help me how to put this code in excel as I am very new to VBA.

Thanks a lot.

 

[namliam]

Erm... I just gave you the code I would put in the excel ???

What are you confused about?

 

[santoshdream]

Hi

sorry for the confusion.

i just want to know that how would I use this code in excel.

please help.

Thanks

 

[Vader]

alt+F11 -> Opens Visual Basic Editor
On the left side of the window Project - VBAProject -> Select sheet1 (or the name of your sheet), then on the right side in the first combo select Worksheet & in the second Change -> enter your code

 

[namliam]

http://www.access-programmers.co.uk/forums/showthread.php?t=172224

 

[namliam]

http://www.access-programmers.co.uk/forums/showthread.php?p=845597#post845597

 

[santoshdream]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = ActiveSheet.Range("a1:a20")
If Not Intersect(Target, rng) Is Nothing And Target.Cells.Value > 25000 Then
MsgBox "Please enter the value less than 25000!"
End If
End Sub

above mention code is working fine but just need little modification in this code

1. when i am copying any value from other sheet to my sheet its not working it is showing "Run time error '13'" and type mismatch error" and also It will paste any value more than 25000 without any error.

2. I want that more than 25000 should not enter in the cell. this code is only giving error message but still user can enter the value in the cell.

Please help urgently.
thanks a lot.

 

[Vader]

Put this after the first code in sheet1

Private Sub Worksheet_Calculate()
Dim rng As Range
Set rng = ActiveSheet.Range("a1:a20")
If Not Intersect(Target, rng) Is Nothing And Target.Cells.Value > 25000 Then
MsgBox "Please enter the value less than 25000!"
End If
End Sub

 

[santoshdream]

Hello,

I have attached my excel sheet please review the error and in this sheet user can easily enter the value more than 25000.

and its still giving the error when i am pasting any value from other sheet which is more than 25000.

thanks

 

[Brianwarnock]

Incorporate

Target.Cells.Value = 0
Target.Cells.Select

after the Then and before the msgbox

Brian

 

[namliam]

It seems to work OK for me?

 

[Brianwarnock]

It seems to work OK for me?

Is that with or without my addition, without it the user can enter any number and just ignore the message.

Brian

 

[namliam]

Yes, the error can be ignored, but the message pops up just fine.

I didnt read that requirement into his? reponse though.... that could very well be me, I have brainfreeze and am burned for the day.