Modeless but waiting form

[Zdenek Moravec]

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

 

[Bob Phillips]

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)

 

[Patrick Molloy]

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

 

[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

 

[Zdenek Moravec]

Hello Bob
There is one main procedure:
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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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)