允许用户在运行时在工作表上进行输入 [英] Allow user to make entries on worksheet during run-time

问题描述

全部:

预先感谢您，你们所有人都是宝贵的资源！！！

我有几个电子表格，其中工作表受到保护，但用户仍然可以使用过滤器.我正在自动处理大多数工作表，但是我需要做的是向用户呈现需要过滤的工作表，然后让他们选择完成"类型的按钮或工具栏条目，我已经拥有了./p>

我需要做的是调出工作表，如果可能的话，在他们进行更改时暂停宏(最多可以在工作表准备好之前选择5个过滤器.

然后，仅将可见的单元格复制到特定的工作表，然后继续执行宏.

我认为工作表更改事件不会做到这一点.

我在想更多的事情，例如在备用纸上设置一个标志，启动下一个宏，然后查看它是否可以找到原始宏并在被标记的地方拾取?

我想到了一种无模式的用户窗体，用户可以单击确定"，然后调用下一个宏，但这不起作用.

呼叫代码是:

  UserForm3.ShowCopyToDisplay"AEP"LastPos = LastPos +1 

AEP是要从中复制过滤出的行的工作表名称.

显示用户窗体，但是单击确定"不会执行任何操作，当然，宏会继续运行.

任何建议将不胜感激！

谢谢

杰夫

解决方案

Jeff让我们尝试一下.您当前的代码:

  UserForm3.ShowCopyToDisplay"AEP"LastPos = LastPos +1 

当我们显示UserForm时，默认行为是 vbModal ，这实际上冻结了应用程序，用户无法与但用户窗体进行任何交互，这不是您所需要的想.您需要的是一种显示表单的方法，然后仅等待用户发信号说输入已完成.

因此，我们需要修改一些内容:

UserForm需要有效地暂停"，同时还允许用户与工作表进行交互. vbModal 表单不能做到这一点(它暂停，没有交互)，而 vbModeless 表单也不能(它继续执行并允许交互).

难题?不.我们可以使用 vbModeless 表单模拟暂停，并保留用户与工作表交互的能力.两全其美！！

我们将显示带有可选的 vbModeless 的表单，这使用户可以与应用程序的其余部分/worksheets/etc进行交互.由于无模式形式会继续执行代码，因此我们需要模拟一个暂停，并可以使用 Loop 来执行此操作.该循环将无限期地运行，并且仅在关闭UserForm后才会中断，此时其余代码将继续执行.

  UserForm3.Show vbModeless做UserForm3.VisibleDoEvents环形LastPos = LastPos +1'如果过滤器/自动过滤器影响了这些变量，则可能需要重置一些变量. 

在设计上，为表单提供一个 Label 控件，并在其中设置其 .Caption 属性(以及表单的 .Caption 属性)一些有用的/指导性的方式.您可以添加命令按钮，但这似乎是不必要的，因为所有按钮所要做的就是调用 Terminate 事件(可以始终使用红色的"X"完成该事件)

对于您的复制问题(明显的粘贴失败)，请尝试更改此内容:

  Sheets("AEP").Select使用ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).目的地:= Sheets("Display").range("A"& LastPos).AutoFilterMode =假Application.CutCopyMode =假结束于 

对此:

 使用ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).复制Sheets("Display").range("A"& LastPos).PasteSpecial.AutoFilterMode =假Application.CutCopyMode =假结束于 

All:

Thank you in advance, you all have been a tremendous resource!!!

I have a couple of spreadsheets where the sheet is protected, but users can still use filters. I'm processing most of the sheets automatically, but what I need to do, is present the user with the sheets that need to be filtered, then have them select a "Finish" type button or toolbar entry, which I already have.

What I need to be able to do, is to bring this sheet up, pause the macro, if possible, while they make their changes (could be up to 5 filters that they select before the sheet is ready.

Then, copy the visible cells only to a specific sheet and then resume the macro.

I don't think that Worksheet change event will do this.

I'm thinking more on the lines of maybe setting a flag on a spare sheet, firing up the next macro and then see if it can find the original macro and pick up where it is flagged?

I thought about a modeless userform that the user could click OK on and then call the next macro, but that does not work.

The calling code is:

UserForm3.Show
CopyToDisplay "AEP"
LastPos = LastPos + 1

Where AEP is the sheet name to copy the filtered rows from.

Userform displays, but clicking ok does nothing and of course, the macro keeps on going.

Any suggestions would be greatly appreciated!

Thanks,

Jeff

解决方案

Jeff let's try this. Your current code:

UserForm3.Show
CopyToDisplay "AEP"
LastPos = LastPos + 1

When we display a UserForm, the default behavior is vbModal, which essentially freezes the application and the user cannot interact with anything but the UserForm, that is not what you want. What you need is a way to display the form, and then just wait for the user to signal that s/he is finished with the input.

So we need to modify a few things:

The UserForm needs to effectively "pause" while also allowing the user to interact with the worksheet. A vbModal form can't do this (it pauses, without interaction), and really neither can a vbModeless (it continues execution AND allows interaction).

Conundrum? No. we can simulate a pause with the vbModeless form, and preserve the user's ability to interact with the sheet. The best of both worlds!!

We will show the form with the optional vbModeless, this allows the user to interact with the rest of the Application /worksheets/etc. Since a modeless form continues code execution, we need to simulate a pause and we can do this with a Loop. The loop will run indefinitely, and only break once the UserForm is closed, at which point the rest of the code will continue to execute.

UserForm3.Show vbModeless 

Do While UserForm3.Visible
    DoEvents
Loop

LastPos = LastPos + 1

'You MAY need to reset some variables, if the Filter/Autofilter has affected these/etc.

Design-wise, give your form a single Label control and set its .Caption property (and the form's .Caption property) in some useful/instructive way. You could add a command button but that seems unnecessary, since all the button would do is invoke the Terminate event (which can always be done with the red "X")

For your issue with copying (apparent failure to paste), try changing this:

Sheets("AEP").Select  

With ActiveSheet      
    .UsedRange.SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("Display").range("A" & LastPos)     
    .AutoFilterMode = False     
    Application.CutCopyMode = False  
End With

To this:

With ActiveSheet      
    .UsedRange.SpecialCells(xlCellTypeVisible).Copy 
     Sheets("Display").range("A" & LastPos).PasteSpecial
    .AutoFilterMode = False     
    Application.CutCopyMode = False  
End With

这篇关于允许用户在运行时在工作表上进行输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！