ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Code needed to populate a message box during a macro (https://www.excelbanter.com/excel-programming/454263-code-needed-populate-message-box-during-macro.html)

Norbert[_4_]

Code needed to populate a message box during a macro
 
Hi,

can someone please help me with some code which I'd like to insert into a macro of mine, which populates a message with text "Please check, if all data in the table is for the date in question!"
There should be a Yes and a No button available (if that is possible).
Yes, should make the macro to run again but the No should take the person to that
particular table in the macro, where the macro is busy at that moment in time.
In other words, the macro should change the view to the current worksheet on which it is busy at that particular moment in time.

I am using: Application.ScreenUpdating = False, to suppress the flickering of the screen and to speed up the process.

In my case it is sheet: 'downtime database'

I hope someone can do, what I can't!

Thanks a lot, in advance
Norbert


Claus Busch

Code needed to populate a message box during a macro
 
Hi Norbert,

Am Thu, 4 Apr 2019 04:19:53 -0700 (PDT) schrieb Norbert:

can someone please help me with some code which I'd like to insert into a macro of mine, which populates a message with text "Please check, if all data in the table is for the date in question!"
There should be a Yes and a No button available (if that is possible).
Yes, should make the macro to run again but the No should take the person to that
particular table in the macro, where the macro is busy at that moment in time.
In other words, the macro should change the view to the current worksheet on which it is busy at that particular moment in time.


try:

Dim ans As Integer

ans = MsgBox("Please check, if all data in the table is for the date in question!", _
vbYesNo, "Warning")

If ans = vbNo Then
Application.Goto Sheets("downtime database").Range("A1")
Exit Sub
End If


Regards
Claus B.
--
Windows10
Office 2016

Norbert[_4_]

Code needed to populate a message box during a macro
 
On Thursday, 4 April 2019 13:40:43 UTC+2, Claus Busch wrote:
Hi Norbert,

Am Thu, 4 Apr 2019 04:19:53 -0700 (PDT) schrieb Norbert:

can someone please help me with some code which I'd like to insert into a macro of mine, which populates a message with text "Please check, if all data in the table is for the date in question!"
There should be a Yes and a No button available (if that is possible).
Yes, should make the macro to run again but the No should take the person to that
particular table in the macro, where the macro is busy at that moment in time.
In other words, the macro should change the view to the current worksheet on which it is busy at that particular moment in time.


try:

Dim ans As Integer

ans = MsgBox("Please check, if all data in the table is for the date in question!", _
vbYesNo, "Warning")

If ans = vbNo Then
Application.Goto Sheets("downtime database").Range("A1")
Exit Sub
End If


Regards
Claus B.
--
Windows10
Office 2016


Dear Claus,
thank you very much for your help!
It works kind off, but not yet 100% to my satisfaction.

I figured something else, what could make the appearance of a message box obsolete and what would actually be a better solution.

I am currently using this code in order to make a selection:

ActiveCell.Offset(0, 1).Range("A1:C22").Select

As you can see, it will ALWAYS select 22x3 cells, but it is not always 22 rows
which have to be selected.

I am also using a variable DateToFind
Dim DateToFind As Date

I basically have to select the 3 cells to the right of the DateToFind
There is a table, sorted by date. The dates are in Column A and every day can have various amounts of rows.
I have to select the 3 cells to the right of the variable DateToFind.
So, the DateToFind is in column A and there might be any number of entries, up
to 22 (behing each other, as it is sorted by date).

Hope I have expressed myself to be understood.

Norbert[_4_]

Code needed to populate a message box during a macro
 
On Thursday, 4 April 2019 15:49:27 UTC+2, Norbert wrote:
On Thursday, 4 April 2019 13:40:43 UTC+2, Claus Busch wrote:
Hi Norbert,

Am Thu, 4 Apr 2019 04:19:53 -0700 (PDT) schrieb Norbert:

can someone please help me with some code which I'd like to insert into a macro of mine, which populates a message with text "Please check, if all data in the table is for the date in question!"
There should be a Yes and a No button available (if that is possible).
Yes, should make the macro to run again but the No should take the person to that
particular table in the macro, where the macro is busy at that moment in time.
In other words, the macro should change the view to the current worksheet on which it is busy at that particular moment in time.


try:

Dim ans As Integer

ans = MsgBox("Please check, if all data in the table is for the date in question!", _
vbYesNo, "Warning")

If ans = vbNo Then
Application.Goto Sheets("downtime database").Range("A1")
Exit Sub
End If


Regards
Claus B.
--
Windows10
Office 2016


Dear Claus,
thank you very much for your help!
It works kind off, but not yet 100% to my satisfaction.

I figured something else, what could make the appearance of a message box obsolete and what would actually be a better solution.

I am currently using this code in order to make a selection:

ActiveCell.Offset(0, 1).Range("A1:C22").Select

As you can see, it will ALWAYS select 22x3 cells, but it is not always 22 rows
which have to be selected.

I am also using a variable DateToFind
Dim DateToFind As Date

I basically have to select the 3 cells to the right of the DateToFind
There is a table, sorted by date. The dates are in Column A and every day can have various amounts of rows.
I have to select the 3 cells to the right of the variable DateToFind.
So, the DateToFind is in column A and there might be any number of entries, up
to 22 (behing each other, as it is sorted by date).

Hope I have expressed myself to be understood.


Here is the piece of code I have to have changed:

Dim DateToFind As Date

Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Let DateToFind = ActiveCell.Value
Cells.Find(What:=DateToFind, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate

ActiveCell.Offset(0, 1).Range("A1:C22").Select




All times are GMT +1. The time now is 12:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com