I am browsing with my macro through a list of items with quantities
and I am showing a user form with Yes-No buttons to confirm deletion
of zero quantity records.
The request now is to make the form modeless to be able to browsing in
the sheet before clicking Yes or No in the form.
When I set the form as modeless, user can switch to sheet and browse
the list, but the macro continues in processing. Then the macro does
not know, what user clicked, so the rest of macro does not work.
Example:
for r = 1 to lastcell
if cells(r,1) = 0 then
'show the modeless form and wait for Yes/No confirmation
if ClickedYes then
'delete the record
end if
end if
next r

Thank you
Zdenek Moravec

Zdenek,

When you say the macro continues processing, what macro do you refer to?
The form should be event driven, nothing happens until a control is clicked.
You could add a button that the user clicks to say that he/she has
completed.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Zdenek Moravec" wrote in message
om...
I am browsing with my macro through a list of items with quantities
and I am showing a user form with Yes-No buttons to confirm deletion
of zero quantity records.
The request now is to make the form modeless to be able to browsing in
the sheet before clicking Yes or No in the form.
When I set the form as modeless, user can switch to sheet and browse
the list, but the macro continues in processing. Then the macro does
not know, what user clicked, so the rest of macro does not work.
Example:
for r = 1 to lastcell
if cells(r,1) = 0 then
'show the modeless form and wait for Yes/No confirmation
if ClickedYes then
'delete the record
end if
end if
next r

Thank you
Zdenek Moravec

for r = 1 to lastcell
if cells(r,1) = 0 then
if msgbox("Delete?",vbYesNo,"Zero Value") = vbYes Then
'delete the record
end if
end if
next r

Patrik Molloy
Microsoft Excel MVP
-----Original Message-----
I am browsing with my macro through a list of items with
quantities
and I am showing a user form with Yes-No buttons to
confirm deletion
of zero quantity records.
The request now is to make the form modeless to be able
to browsing in
the sheet before clicking Yes or No in the form.
When I set the form as modeless, user can switch to
sheet and browse
the list, but the macro continues in processing. Then
the macro does
not know, what user clicked, so the rest of macro does
not work.
Example:
for r = 1 to lastcell
if cells(r,1) = 0 then
'show the modeless form and wait for Yes/No
confirmation
if ClickedYes then
'delete the record
end if
end if
next r

Thank you
Zdenek Moravec
.

Hello Patrick
The MsgBox statement does not allow to browse in the sheet, the using of
whole Excel is disabled; you must first click the button. I need to be
able to switch to Excel and browse the sheet before click.
Zdenek Moravec



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hello Bob
There is one main procedu
sub main()
for r = 1 to lastcell
if cells(r,1) = 0 then
MyYesNoForm.Show vbModeless
if Confirmed=True then
'delete the record
end if
end if
next r
end sub
and the User form code contains two procedures:
Private Sub ButtonYes_Click()
Confirmed = True
Unload Me
End Sub
Private Sub ButtonNo_Click()
Confirmed = False
Unload Me
End Sub
I thought, that when the form is displayed, use can swith to sheet. Then
after clicking Yes or No button, event procedure (i.e.
ButtonYes_Click()) is started and 'Confirmed' variable is set.My idea is
wrong.
Zdenek Moravec

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Zdenec,

I think you should use Patrick's suggestion, forget the userform and just
use MsgBox.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Zdenek Moravec" wrote in message
...
Hello Bob
There is one main procedu
sub main()
for r = 1 to lastcell
if cells(r,1) = 0 then
MyYesNoForm.Show vbModeless
if Confirmed=True then
'delete the record
end if
end if
next r
end sub
and the User form code contains two procedures:
Private Sub ButtonYes_Click()
Confirmed = True
Unload Me
End Sub
Private Sub ButtonNo_Click()
Confirmed = False
Unload Me
End Sub
I thought, that when the form is displayed, use can swith to sheet. Then
after clicking Yes or No button, event procedure (i.e.
ButtonYes_Click()) is started and 'Confirmed' variable is set.My idea is
wrong.
Zdenek Moravec

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Bob
There is the problem, that the MsgBox statement does not allow to browse
in the sheet, the using of whole Excel is disabled; you must first click
the button. I need to be able to switch to Excel and browse the sheet
before click.
Zdenek Moravec

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Zdenec,

Back to the original point then<vbg

How about an approach where the form dynamically adds checkboxes and a label
for each row to be deleted. This would be built in the initialisation, and
there can be a user confirm button that they can press when happy?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Zdenek Moravec" wrote in message
...
Bob
There is the problem, that the MsgBox statement does not allow to browse
in the sheet, the using of whole Excel is disabled; you must first click
the button. I need to be able to switch to Excel and browse the sheet
before click.
Zdenek Moravec

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!