Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mimic Value Being Typed Into Cell
via VBA, I need to mimic someone writing a value into a cell. Just
changing the value via Range.Value doesn't fire the event I need, so I need it to behave the same way it does one someone manually enters the value. Does anyone know how to do this from code? thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mimic Value Being Typed Into Cell
Matthew,
Since it's to simple to be overseen I'm not sure whether this will help, but it does somehow run an "event procedure" (in this case EventSub) after "entering" (via VB) a value in a cell. Just follow entering the line in which you give the cell a value with a call to the "event procedure" you want after running the cell, and do that Sub TryIt() ActiveCell.Value = 25 Call EventSub End Sub Private Sub EventSub() MsgBox "Fake Event fired" End Sub -- Regards, Auk Ales * Please reply to this newsgroup only * * I will not react on unsolicited e-mails * "Matthew Wieder" wrote in message ... via VBA, I need to mimic someone writing a value into a cell. Just changing the value via Range.Value doesn't fire the event I need, so I need it to behave the same way it does one someone manually enters the value. Does anyone know how to do this from code? thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mimic Value Being Typed Into Cell
This mimics the keyboard :-
SendKeys "ABCDE", True SendKeys "{ENTER}", Tru -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mimic Value Being Typed Into Cell
What event do you claim isn't being fired. Perhaps you have disabled events, because the change event fires if the cell value is changed either manually or by code. If you are using selection change, then you can have your code select the cell if you need to fire the event. -- Regards, Tom Ogilvy "Matthew Wieder" wrote in message ... via VBA, I need to mimic someone writing a value into a cell. Just changing the value via Range.Value doesn't fire the event I need, so I need it to behave the same way it does one someone manually enters the value. Does anyone know how to do this from code? thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mimic Value Being Typed Into Cell
It's the OnEntry handler that has been setup by an add-in. Just
changing the cell value doesn't trigger the event, but I think a combination of setting the activecell and calling the handler with Call should do the trick... thanks! Tom Ogilvy wrote: What event do you claim isn't being fired. Perhaps you have disabled events, because the change event fires if the cell value is changed either manually or by code. If you are using selection change, then you can have your code select the cell if you need to fire the event. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mimic Value Being Typed Into Cell
I'm not sure I understand your response - you say that "No, setting the
activecell and calling the handler won't work" but then you say the solution is "have it call the handler as well." In your understanding, can I call the OnEntry handler or not from my code automating the cell value change? thanks, -Matt Tom Ogilvy wrote: Onentry only fires when the cell is edited manually. No, setting the activecell and calling the handler won't work unless you mean selecting the cell and having the change event call the handler. However, this would fire before the cell is edited which is probably not what you want. If you are having code modify the cell, then have it call the handler as well. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mimic Value Being Typed Into Cell
That is why we have paragraphs.
the SelectionChange event can call the macro you assigned to onentry, but it would call it before the cell value has been changed. The code that changes the value in the cell could call the macro assigned to your onentry event. Two different thoughts. The "It" in the second sentence you cite was referring to the subject of the first clause in the same sentence, not the first sentence in the preceding paragraph. If the selectionchange event is the macro which modifies the cell, then you will have to sort that out, but it doesn't change the answer. -- Regards, Tom Ogilvy "Matthew Wieder" wrote in message ... I'm not sure I understand your response - you say that "No, setting the activecell and calling the handler won't work" but then you say the solution is "have it call the handler as well." In your understanding, can I call the OnEntry handler or not from my code automating the cell value change? thanks, -Matt Tom Ogilvy wrote: Onentry only fires when the cell is edited manually. No, setting the activecell and calling the handler won't work unless you mean selecting the cell and having the change event call the handler. However, this would fire before the cell is edited which is probably not what you want. If you are having code modify the cell, then have it call the handler as well. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mimic Value Being Typed Into Cell
and therein lies the clarification - we hadn't been discussing the
selectionchange event so I didn't understand that was your point of reference. In any event, I think we're on the same page now, so thanks for yoru help. Tom Ogilvy wrote: That is why we have paragraphs. the SelectionChange event can call the macro you assigned to onentry, but it would call it before the cell value has been changed. The code that changes the value in the cell could call the macro assigned to your onentry event. Two different thoughts. The "It" in the second sentence you cite was referring to the subject of the first clause in the same sentence, not the first sentence in the preceding paragraph. If the selectionchange event is the macro which modifies the cell, then you will have to sort that out, but it doesn't change the answer. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mimic Value Being Typed Into Cell
Hi Matthew,
Thanks for your quickly reply! Thanks for posting in the community. First of all, I would like to confirm my understanding of your issue. From your description, I understand that you hope when the change the value in the cell, a event will be fired. Have I fully understood you? If there is anything I misunderstood, please feel free to let me know. I agree with TOM's suggestion. Here is my test code. Sub Test() 'run the macro first to set the OnEntry ThisWorkbook.ActiveSheet.OnEntry = "Hello" End Sub Sub Hello() MsgBox "hello" End Sub Sub sdaf() 'This will not fire the Hello() Macro Application.ActiveSheet.Cells(1, 1) = "fdsf" End Sub Also have you tried to hook the SheetChange event, which will be fired when the cell was changed by manual or by coding. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) MsgBox "Sheet changed" End Sub Please apply my suggestion above and let me know if it helps resolve your problem. Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mimic Value Being Typed Into Cell
Hi Matthew,
Thanks for posting in the community. Did my suggestion help you? If you still have any question on this issue,please feel free to let me know. Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Cell Formula to mimic text to columns. | Excel Worksheet Functions | |||
Custom Cell format to mimic time format | Excel Discussion (Misc queries) | |||
Data entry - Copy contents of cell typed in one cell to another ce | Excel Worksheet Functions | |||
How do I get a - when a 0 is typed in a cell? | Excel Discussion (Misc queries) | |||
how do I make a word typed in a cell go to a specific cell in anot | Excel Discussion (Misc queries) |