Refedit vba excel свойства и методы
The RefEdit Control by default is not available on the Control Toolbox. To locate and add this Control to the Toolbox, right click over any Control on the Toolbox, select Additional Controls and scroll down until you see the Control RefEdit.Ctrl. Simply check the box and click OK. Unfortunately, there is no documentation in the Excel Help for this Control.
Here is an overview of this Control
Normally used on a user form, the RefEdit control will display the address of a range, or single cell, that you've entered (typed in) or selected, while the UserForm is collapsed, on one or more worksheets. To select a range, click the button in the control to collapse the UserForm , select the range, and then click the button in the control again to expand the UserForm. To select non-contiguous ranges, hold down the Ctrl key or use the comma (or your windows default argument separator) to separate ranges.
If the RefEdit control is embedded on a worksheet, you can link the contents of the control to a cell on any worksheet in that workbook.
The default property for a RefEdit control is Value.
The default event for a RefEdit control is BeforeDragOver.
You cannot use a RefEdit control on a modeless user form (applies only to Excel 2000+). You can use the ShowModal property to set a user form to modal.
End of Overview
Basically, the RefEdit Control works in the same way as the Collapse Dialog buttons do in many of Excel's built-in Dialog and Edit boxes. If you are unsure what I mean by this, on an Excel Worksheet activate the Function Wizard, select any Function and click OK and you will notice that each argument box on the right hand side has a small button with an arrow on the right hand side. If you click on this button, the main dialog box disappears, giving you a clearer view of your spreadsheet and you are easily able to use your mouse pointer to select various cells. Clicking the button again expands the original dialog box, bringing things back to how they originally were.
Lets now use the RefEdit Control on a UserForm and see how we can use it to allow the user to apply formatting to a specified range of cells on a worksheet. To do this, follow the steps as shown below:
The following example illustrates how to create a UserForm that contains a RefEdit control and a CommandButton control. When the UserForm appears, you enter a reference in the RefEdit control, you do this using your mouse pointer. You then click the CommandButton and the range that you referenced is formatted with a red background, a thick black outline and any font is bolded.
Sub CommandButton1_Click()
Dim rRange As Range
Dim strAddr As String
'Get the address, or reference, from the RefEdit control.
'Note that a String is returned by the Value Property
strAddr = RefEdit1.Value
'Set the rRange Range variable to the range nominated by the
'RefEdit control. If the Sheet name is also include (eg Sheet2!A1:A10)
'It will act on that range, even if the sheet is not active at the time.
Set rRange = Range(strAddr)
'Apply a red pattern to the SelRange.
'Bold any font and apply a thick border
With rRange
.Interior.ColorIndex = 3
.Font.Bold = True
.BorderAround LineStyle:=xlContinuous, Weight:=xlThick
End With
As commented in the code above, you should note that the Value returned by the RefEdit Control is always a String. This means that we can use it only to collect the range address of the cells as a String and not directly as a Range Object. You may also have noted that the Worksheet name is included in the selection, which means we do not need to know which sheet the user has selected to perform our operation on, as it is part of the address.
When designing a UserForm which requires the user to nominate a range of cells, it is prudent to use the RefEdit Control so we can be sure the user has a good chance of nominating the correct range. Leaving the user to type things such as dates, addresses etc., is almost always prone to error as the format in which the text is typed is critical and Murphy's law states that if they can get it wrong, they probably will.
Even when we use a RefEdit Control to collect the range from a user, we should still check to ensure that the address returned to the RefEdit Control as a string is correct. The easiest way for us to do this is to use the IsObject Function which simply returns a Boolean Value indicating whether an identifier (an element of an expression that refers to a Constant or Variable) represents an Object Variable. To do this we would modify the original code as shown below:
Sub CommandButton1_Click()
Dim rRange As Range
Dim strAddr As String
Dim bIsRange As Boolean
'Get the address, or reference, from the RefEdit control.
'Note that a String is returned by the Value Property
strAddr = RefEdit1.Value
'Use IsObject to find out if the string is a valid address.
On Error Resume Next
bIsRange = IsObject(Range(strAddr))
On Error GoTo 0
If bIsRange = False Then 'Not Valid
MsgBox "The range is not valid"
RefEdit1.Value = vbNullString
RefEdit1.SetFocus
Exit Sub
End If
'Set the rRange Range variable to the range nominated by the
'RefEdit control. If the Sheet name is also include (eg Sheet2!A1:A10)
'It will act on that range, even if the sheet is not active at the time.
Set rRange = Range(strAddr)
'Apply a red pattern to the SelRange.
'Bold any font and apply a thick border
With rRange
.Interior.ColorIndex = 3
.Font.Bold = True
.BorderAround LineStyle:=xlContinuous, Weight:=xlThick
End With
Notice that also in this case we must use the On Error Resume Next statement or else our Procedure would return a run-time error if strAddr was not a valid address string.
In Excel 97 and also to some degree 2000, the RefEdit Control contains numerous problems. The main one being that if the user collapsed the button and then clicked the X in the top right hand control of the collapsed RefEdit Control, the UserForm and the RefEdit Control would both disappear and lock up the PC. This was particularly true when the RefEdit was used within a Frame Control on a UserForm. This problem appears to have been fixed within Excel XP.
While the default Event for the RefEdit Control is the BeforeDragOver it will be unlikely that you will ever use this. The text below is from the Excel help and does not even mention that it applies to the RefEdit Control. You will also note the the arguments for the BeforeDragOver Event are different to any that are shown below. According to Microsoft this Event applies to CheckBox control, ComboBox control, CommandButton control, Frame control, Image control, Label control, ListBox control, MultiPage control, OptionButton control, ScrollBar control, SpinButton control, TabStrip control, TextBox control, ToggleButton control, UserForm object. Just another of Microsoft's mysteries :o)
BeforeDragOver Event From Excel Help
For Frame
Private Sub object_BeforeDragOver( ByVal Cancel As MSForms.ReturnBoolean, ctrl As Control, ByVal Data As DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As fmShiftState)
For MultiPage
Private Sub object_BeforeDragOver( index As Long, ByVal Cancel As MSForms.ReturnBoolean, ctrl As Control, ByVal Data As DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As fmShiftState)
For TabStrip
Private Sub object_BeforeDragOver( index As Long, ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As fmShiftState)
For other controls
Private Sub object_BeforeDragOver( ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As fmShiftState)
The BeforeDragOver event syntax has these parts:
Part | Description |
---|---|
object | Required. A valid object name. |
index | Required. The index of the Page in a MultiPage that the drag-and-drop operation will affect. |
Cancel | Required. Event status. False indicates that the control should handle the event (default). True indicates the application handles the event. |
ctrl | Required. The control being dragged over. |
Data | Required. Data that is dragged in a drag-and-drop operation. The data is packaged in a DataObject. |
X, Y | Required. The horizontal and vertical coordinates of the control's position. Both coordinates are measured in points. X is measured from the left edge of the control; Y is measured from the top of the control.. |
DragState | Required. Transition state of the data being dragged. |
Effect | Required. Operations supported by the drop source. |
Shift | Required. Specifies the state of SHIFT, CTRL, and ALT. |
Constant | Value | Description |
---|---|---|
fmDragStateEnter | 0 | Mouse pointer is within range of a target. |
fmDragStateLeave | 1 | Mouse pointer is outside the range of a target. |
fmDragStateOver | 2 | Mouse pointer is at a new position, but remains within range of the same target. |
Constant | Value | Description |
---|---|---|
fmDropEffectNone | 0 | Does not copy or move the drop source to the drop target. |
fmDropEffectCopy | 1 | Copies the drop source to the drop target. |
fmDropEffectMove | 2 | Moves the drop source to the drop target. |
fmDropEffectCopyOrMove | 3 | Copies or moves the drop source to the drop target. |
Constant | Value | Description |
---|---|---|
fmShiftMask | 1 | SHIFT was pressed. |
fmCtrlMask | 2 | CTRL was pressed. |
fmAltMask | 4 | ALT was pressed. |
Use this event to monitor the mouse pointer as it enters, leaves, or rests directly over a valid target. When a drag-and-drop operation is in progress, the system initiates this event when the user moves the mouse, or presses or releases the mouse button or buttons. The mouse pointer position determines the target object that receives this event. You can determine the state of the mouse pointer by examining the DragState argument.
When a control handles this event, you can use the Effect argument to identify the drag-and-drop action to perform. When Effect is set to fmDropEffectCopyOrMove, the drop source supports a copy (fmDropEffectCopy), move (fmDropEffectMove), or a cancel (fmDropEffectNone) operation.
When Effect is set to fmDropEffectCopy, the drop source supports a copy or a cancel (fmDropEffectNone) operation.
When Effect is set to fmDropEffectMove, the drop source supports a move or a cancel (fmDropEffectNone) operation.
When Effect is set to fmDropEffectNone. the drop source supports a cancel operation.
Most controls do not support drag-and-drop while Cancel is False, which is the default setting. This means the control rejects attempts to drag or drop anything on the control, and the control does not initiate the BeforeDropOrPaste event. The TextBox and ComboBox controls are exceptions to this; these controls support drag-and-drop operations even when Cancel is False.
The ByVal keyword in Microsoft Forms indicates that an argument is passed as a value; this is the standard meaning of ByVal in Visual Basic. However, in Microsoft Forms, you can use ByVal with a ReturnBoolean, ReturnEffect, ReturnInteger, or ReturnString object. When you do, the value passed is not a simple data type; it is a pointer to the object.
When used with these objects, ByVal refers to the object, not the method of passing parameters. Each of the objects listed above has a Value property that you can set. You can also pass that value into and out of a function. Because you can change the values of the object's members, events produce results consistent with ByRef behavior, even though the event syntax says the parameter is ByVal.
Assigning a value to an argument associated with a ReturnBoolean, ReturnEffect, ReturnInteger, or ReturnString is no different from setting the value of any other argument. For example, if the event syntax indicates a Cancel argument used with the ReturnBoolean object, the statement Cancel=True is still valid, just as it is with other data types.
UserForm.RefEdit – это элемент управления пользовательской формы, предназначенный для получения и редактирования ссылок на диапазон ячеек путем выделения диапазона на рабочем листе или вручную в поле редактора ссылок.
Кнопка справа от поля ввода и редактирования ссылок предназначена для сворачивания пользовательской формы в «одну строку». На свернутой форме остается только элемент управления RefEdit и становится доступным ввод адреса диапазона путем выделения ячеек на рабочем листе.
В стандартном наборе инструментов пользовательской формы Toolbox элемента управления RefEdit нет. Чтобы его добавить, кликните правой кнопкой мыши на Toolbox и выберите ссылку «Additional Controls…»:
В окне Additional Controls найдите строку RefEdit Ctrl:
Выберите строку RefEdit Ctrl и нажмите кнопку «OK». Значок элемента управления RefEdit будет добавлен на Toolbox (последний в списке):
Свойства элемента RefEdit
Почти все свойства элемента управления RefEdit аналогичны свойствам текстового поля (TextBox). Со всеми доступными свойствами редактора ссылок вы можете ознакомиться в окне Properties элемента управления RefEdit.
Основное свойство RefEdit, являющееся свойством по умолчанию, – это Text. Как и у текстового поля, свойство Text элемента RefEdit дублируется свойством Value. Если в окне Properties начать набор символов в поле свойства Text, ввод будет автоматически дублироваться в поле свойства Value.
Использование редактора ссылок
Открываем пользовательскую форму с элементом управления RefEdit:
Нажимаем на кнопку справа и выбираем диапазон на рабочем листе:
Полный адрес включает название листа и абсолютный адрес выбранного диапазона ячеек. После повторного нажатия на кнопку справа от поля ввода пользовательская форма принимает первоначальный вид:
Проверяем, что свойство Text редактора ссылок является свойством по умолчанию:
Создайте или откройте файл Excel с расширением .xlsm (Книга Excel с поддержкой макросов) или с расширением .xls в старых версиях приложения.
Перейдите в редактор VBA, нажав сочетание клавиш «Левая_клавиша_Alt+F11».
В открывшемся окне редактора VBA выберите вкладку «Insert» главного меню и нажмите кнопку «UserForm». То же подменю откроется при нажатии на вторую кнопку (после значка Excel) на панели инструментов.
На экране редактора VBA появится новая пользовательская форма с именем «UserForm1»:
Добавление элементов управления
Обычно вместе с пользовательской формой открывается панель инструментов «Toolbox», как на изображении выше, с набором элементов управления формы. Если панель инструментов «Toolbox» не отобразилась, ее можно вызвать, нажав кнопку «Toolbox» во вкладке «View»:
При наведении курсора на элементы управления появляются подсказки.
Найдите на панели инструментов «Toolbox» элемент управления с подсказкой «TextBox», кликните по нему и, затем, кликните в любом месте рабочего поля формы. Элемент управления «TextBox» (текстовое поле) будет добавлен на форму.
Найдите на панели инструментов «Toolbox» элемент управления с подсказкой «CommandButton», кликните по нему и, затем, кликните в любом месте рабочего поля формы. Элемент управления «CommandButton» (кнопка) будет добавлен на форму.
Кликнув по элементу управления на форме, вы можете изменять его размер, перетягивая границы за белые квадратики, и перетаскивать по форме, ухватив за одну из границ. Кликнув по заголовку формы или по ее рабочему полю, вы можете также изменять ее размер, перетягивая границы за белые квадратики.
Нажатием клавиши «F4» вызывается окно свойств, с помощью которого можно вручную задавать значения свойств пользовательской формы и элементов управления. В окне свойств отображаются свойства выбранного элемента управления или формы, если выбрана она. Также окно свойств можно вызвать, нажав кнопку «Properties Window» во вкладке «View».
Отображение формы на экране
Чтобы запустить пользовательскую форму для просмотра из редактора VBA, необходимо выбрать ее, кликнув по заголовку или свободному от элементов управления полю, и совершить одно из трех действий:
- нажать клавишу «F5»;
- нажать на треугольник на панели инструментов (на изображении выше треугольник находится под вкладкой «Debug»);
- нажать кнопку «Run Sub/UserForm» во вкладке «Run».
Для запуска пользовательской формы с рабочего листа Excel, можно использовать кнопки, описанные в этой статье. Например, чтобы отобразить форму с помощью «кнопки – элемента ActiveX», используйте в модуле рабочего листа следующий код:
Здравствуйте! У Вас нет темы по работе с фильтром. Можно проконсультироваться на этот счёт?
Здравствуйте, Сергей!
Задавайте вопрос, постараюсь ответить.
Спасибо, Евгений! Имеется в Excel столбец с включённым автофильтром. Можно ли программно запустить окно фильтра для выбора значений? Т.е. не руками нажимать на иконку фильтра, а заставить окно открываться с помощью макроса. Спасибо за ответ.
Да, это возможно:
Сергей, уточните вопрос: нужно программно нажать кнопки Ok и Отмена или отследить, какая из них была нажата?
Просто отследить, какая кнопка нажата.
По предыдущему вопросу. Я вставил sendkeys… в конец макроса, который запускается при нажатии кнопки на листе. Окно фильтра появляется и тут же закрывается. А как сделать, чтобы окно осталось, и пользователь мог выбрать данные?
Евгений, я разобрался, почему не выводилось окно с фильтром.
Пример отслеживания нажатий кнопки «OK» автофильтра. На нажатие кнопки «Отмена» код не реагирует.
Вставьте в стандартный модуль (в примере — Module1) объявление глобальной переменной и код процедуры для кнопки:
Range("A1").CurrentRegion можно заменить на имя таблицы.
Евгений, а формулу в поле B2 оставлять или её можно удалить?
Можно удалить, если в таблице есть другие формулы. Я изменил код процедуры Worksheet_Calculate() в предыдущем примере, чтобы она не реагировала на изменение формул, а только на нажатие кнопки «OK» автофильтра. Range("A1") — это ячейка с кнопкой фильтра.
Евгений, спасибо за корректировку процедуры. Думаю, в ней и поле myString1 необязательно (и связанная проверка с ним), т.к. пользователь может выбрать все значения.
Но… после команды SendKeys "%" событие Worksheet_Calculate() отрабатывается только один раз :(.
Сергей, событие Worksheet_Calculate() не зависит от команды SendKeys "%" . Ограничение стоит здесь:
Код в процедуре Worksheet_Calculate() срабатывает только когда активна ячейка Range("A1") . Если заменить в этой строке Range("A1") на диапазон строки заголовков таблицы, тогда код будет срабатывать при любой активной ячейке в заголовке.
В переменную myString записывается состояние таблицы до применения фильтра, в переменную myString1 — после применения. Затем их содержимое сравнивается: если они содержат разные значения — значит, фильтр был применен.
Здравствуйте! может я не туда пишу. вопрос
нужно посчитать кол-во строк в столбце «В» (от 100 до 5000) и вставить в формулу вместо 744
table = Range("B2:C744")
спасибо.
В столбце «B» не должно быть пустых ячеек до последней строки таблицы.
Как определить в VBA есть узор в ячейке?
Спасибо за достаточно полную информацию.
Был бы очень вам признателен, если бы вы подсказали:
как изменить ширину блоков верхнего колонтитула.
«Введена слишком длинная строка. Уменьшите число знаков.»
При изменении шрифта с полужирного на обычный всё нормально
воспринимается,
Прошу прощения, если не по адресу.
Добрый день! Никак не могу решить проблему с заблокированным автофильтром на защищенном макросом (нашел на форумах) листе. Подскажите пожалуйста, куда и какую строчку нужно добавить в макрос, чтобы при открытии файла был активен автофильтр:
Добрый день, Владислав!
Замените строку
Добрый день, Евгений!
ОГРОМНОЕ СПАСИБО. :))
Здравствуйте, помогите, пожалуйста, решить задачу((
Разработать программу «Кредитный калькулятор» для расчета выплат по кредиту в условиях начисления процентов по аннуитетной схеме постнумерандо. Форма должна предусматривать ввод:
1) сумма кредита, допустимы 2 знака после запятой;
2) процентная ставка (годовая), допустимы 2 знака после запятой;
3) количество периодов (месяцев), целое положительное число.
Также форма должна содержать две кнопки:
1) «Ok» (выполнить расчет, заполнить лист Excel, освободить форму и закончить работу программы);
2) «Отмена» (освободить форму и закончить работу программы).
На лист Excel следует вывести 5 колонок:
1) номер периода;
2) остаток кредита;
3) сумма процентов за пользование кредитом, подлежащая к оплате;
4) сумма погашения основного долга (кредита);
5) общая сумма выплат за период.
Форма должна быть открыта из макроса «main» и там же освобождена по завершению своего использования. Форма должна содержать только код работы с формой и ввода данных. Логика верификации данных должна быть, по возможности, отделена от кода формы. Бизнес-логика обязательно должна быть отделена от кода формы.
Здравствуйте, Рафия!
С разработкой кредитного калькулятора вам помогут только за плату на бирже фриланса.
Добрый день, Евгений! Я только начинаю работать с VBA. Помогите, пожалуйста, прописать код для автоматического перемещения курсора на ячейку вправо после заполнения предыдущей.
Здравствуйте, Ева! Для перемещения фокуса в ячейку справа код VBA не нужен: используйте для этого клавишу «Tab» или настройте клавишу «Enter» для перехода вправо (Файл >> Параметры >> Дополнительно).
Спасибо за ответ! Да, но это надо делать вручную; речь идёт об автоматическом перемещении курсора.
The RefEdit Control by default is not available on the Control Toolbox. To locate and add this Control to the Toolbox, right click over any Control on the Toolbox, select Additional Controls and scroll down until you see the Control RefEdit.Ctrl. Simply check the box and click OK. Unfortunately, there is no documentation in the Excel Help for this Control.
Here is an overview of this Control
Normally used on a user form, the RefEdit control will display the address of a range, or single cell, that you've entered (typed in) or selected, while the UserForm is collapsed, on one or more worksheets. To select a range, click the button in the control to collapse the UserForm , select the range, and then click the button in the control again to expand the UserForm. To select non-contiguous ranges, hold down the Ctrl key or use the comma (or your windows default argument separator) to separate ranges.
If the RefEdit control is embedded on a worksheet, you can link the contents of the control to a cell on any worksheet in that workbook.
The default property for a RefEdit control is Value.
The default event for a RefEdit control is BeforeDragOver.
You cannot use a RefEdit control on a modeless user form (applies only to Excel 2000+). You can use the ShowModal property to set a user form to modal.
End of Overview
Basically, the RefEdit Control works in the same way as the Collapse Dialog buttons do in many of Excel's built-in Dialog and Edit boxes. If you are unsure what I mean by this, on an Excel Worksheet activate the Function Wizard, select any Function and click OK and you will notice that each argument box on the right hand side has a small button with an arrow on the right hand side. If you click on this button, the main dialog box disappears, giving you a clearer view of your spreadsheet and you are easily able to use your mouse pointer to select various cells. Clicking the button again expands the original dialog box, bringing things back to how they originally were.
Lets now use the RefEdit Control on a UserForm and see how we can use it to allow the user to apply formatting to a specified range of cells on a worksheet. To do this, follow the steps as shown below:
The following example illustrates how to create a UserForm that contains a RefEdit control and a CommandButton control. When the UserForm appears, you enter a reference in the RefEdit control, you do this using your mouse pointer. You then click the CommandButton and the range that you referenced is formatted with a red background, a thick black outline and any font is bolded.
Sub CommandButton1_Click()
Dim rRange As Range
Dim strAddr As String
'Get the address, or reference, from the RefEdit control.
'Note that a String is returned by the Value Property
strAddr = RefEdit1.Value
'Set the rRange Range variable to the range nominated by the
'RefEdit control. If the Sheet name is also include (eg Sheet2!A1:A10)
'It will act on that range, even if the sheet is not active at the time.
Set rRange = Range(strAddr)
'Apply a red pattern to the SelRange.
'Bold any font and apply a thick border
With rRange
.Interior.ColorIndex = 3
.Font.Bold = True
.BorderAround LineStyle:=xlContinuous, Weight:=xlThick
End With
As commented in the code above, you should note that the Value returned by the RefEdit Control is always a String. This means that we can use it only to collect the range address of the cells as a String and not directly as a Range Object. You may also have noted that the Worksheet name is included in the selection, which means we do not need to know which sheet the user has selected to perform our operation on, as it is part of the address.
When designing a UserForm which requires the user to nominate a range of cells, it is prudent to use the RefEdit Control so we can be sure the user has a good chance of nominating the correct range. Leaving the user to type things such as dates, addresses etc., is almost always prone to error as the format in which the text is typed is critical and Murphy's law states that if they can get it wrong, they probably will.
Even when we use a RefEdit Control to collect the range from a user, we should still check to ensure that the address returned to the RefEdit Control as a string is correct. The easiest way for us to do this is to use the IsObject Function which simply returns a Boolean Value indicating whether an identifier (an element of an expression that refers to a Constant or Variable) represents an Object Variable. To do this we would modify the original code as shown below:
Sub CommandButton1_Click()
Dim rRange As Range
Dim strAddr As String
Dim bIsRange As Boolean
'Get the address, or reference, from the RefEdit control.
'Note that a String is returned by the Value Property
strAddr = RefEdit1.Value
'Use IsObject to find out if the string is a valid address.
On Error Resume Next
bIsRange = IsObject(Range(strAddr))
On Error GoTo 0
If bIsRange = False Then 'Not Valid
MsgBox "The range is not valid"
RefEdit1.Value = vbNullString
RefEdit1.SetFocus
Exit Sub
End If
'Set the rRange Range variable to the range nominated by the
'RefEdit control. If the Sheet name is also include (eg Sheet2!A1:A10)
'It will act on that range, even if the sheet is not active at the time.
Set rRange = Range(strAddr)
'Apply a red pattern to the SelRange.
'Bold any font and apply a thick border
With rRange
.Interior.ColorIndex = 3
.Font.Bold = True
.BorderAround LineStyle:=xlContinuous, Weight:=xlThick
End With
Notice that also in this case we must use the On Error Resume Next statement or else our Procedure would return a run-time error if strAddr was not a valid address string.
In Excel 97 and also to some degree 2000, the RefEdit Control contains numerous problems. The main one being that if the user collapsed the button and then clicked the X in the top right hand control of the collapsed RefEdit Control, the UserForm and the RefEdit Control would both disappear and lock up the PC. This was particularly true when the RefEdit was used within a Frame Control on a UserForm. This problem appears to have been fixed within Excel XP.
While the default Event for the RefEdit Control is the BeforeDragOver it will be unlikely that you will ever use this. The text below is from the Excel help and does not even mention that it applies to the RefEdit Control. You will also note the the arguments for the BeforeDragOver Event are different to any that are shown below. According to Microsoft this Event applies to CheckBox control, ComboBox control, CommandButton control, Frame control, Image control, Label control, ListBox control, MultiPage control, OptionButton control, ScrollBar control, SpinButton control, TabStrip control, TextBox control, ToggleButton control, UserForm object. Just another of Microsoft's mysteries :o)
BeforeDragOver Event From Excel Help
For Frame
Private Sub object_BeforeDragOver( ByVal Cancel As MSForms.ReturnBoolean, ctrl As Control, ByVal Data As DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As fmShiftState)
For MultiPage
Private Sub object_BeforeDragOver( index As Long, ByVal Cancel As MSForms.ReturnBoolean, ctrl As Control, ByVal Data As DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As fmShiftState)
For TabStrip
Private Sub object_BeforeDragOver( index As Long, ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As fmShiftState)
For other controls
Private Sub object_BeforeDragOver( ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As fmShiftState)
The BeforeDragOver event syntax has these parts:
Part | Description |
---|---|
object | Required. A valid object name. |
index | Required. The index of the Page in a MultiPage that the drag-and-drop operation will affect. |
Cancel | Required. Event status. False indicates that the control should handle the event (default). True indicates the application handles the event. |
ctrl | Required. The control being dragged over. |
Data | Required. Data that is dragged in a drag-and-drop operation. The data is packaged in a DataObject. |
X, Y | Required. The horizontal and vertical coordinates of the control's position. Both coordinates are measured in points. X is measured from the left edge of the control; Y is measured from the top of the control.. |
DragState | Required. Transition state of the data being dragged. |
Effect | Required. Operations supported by the drop source. |
Shift | Required. Specifies the state of SHIFT, CTRL, and ALT. |
Constant | Value | Description |
---|---|---|
fmDragStateEnter | 0 | Mouse pointer is within range of a target. |
fmDragStateLeave | 1 | Mouse pointer is outside the range of a target. |
fmDragStateOver | 2 | Mouse pointer is at a new position, but remains within range of the same target. |
Constant | Value | Description |
---|---|---|
fmDropEffectNone | 0 | Does not copy or move the drop source to the drop target. |
fmDropEffectCopy | 1 | Copies the drop source to the drop target. |
fmDropEffectMove | 2 | Moves the drop source to the drop target. |
fmDropEffectCopyOrMove | 3 | Copies or moves the drop source to the drop target. |
Constant | Value | Description |
---|---|---|
fmShiftMask | 1 | SHIFT was pressed. |
fmCtrlMask | 2 | CTRL was pressed. |
fmAltMask | 4 | ALT was pressed. |
Use this event to monitor the mouse pointer as it enters, leaves, or rests directly over a valid target. When a drag-and-drop operation is in progress, the system initiates this event when the user moves the mouse, or presses or releases the mouse button or buttons. The mouse pointer position determines the target object that receives this event. You can determine the state of the mouse pointer by examining the DragState argument.
When a control handles this event, you can use the Effect argument to identify the drag-and-drop action to perform. When Effect is set to fmDropEffectCopyOrMove, the drop source supports a copy (fmDropEffectCopy), move (fmDropEffectMove), or a cancel (fmDropEffectNone) operation.
When Effect is set to fmDropEffectCopy, the drop source supports a copy or a cancel (fmDropEffectNone) operation.
When Effect is set to fmDropEffectMove, the drop source supports a move or a cancel (fmDropEffectNone) operation.
When Effect is set to fmDropEffectNone. the drop source supports a cancel operation.
Most controls do not support drag-and-drop while Cancel is False, which is the default setting. This means the control rejects attempts to drag or drop anything on the control, and the control does not initiate the BeforeDropOrPaste event. The TextBox and ComboBox controls are exceptions to this; these controls support drag-and-drop operations even when Cancel is False.
The ByVal keyword in Microsoft Forms indicates that an argument is passed as a value; this is the standard meaning of ByVal in Visual Basic. However, in Microsoft Forms, you can use ByVal with a ReturnBoolean, ReturnEffect, ReturnInteger, or ReturnString object. When you do, the value passed is not a simple data type; it is a pointer to the object.
When used with these objects, ByVal refers to the object, not the method of passing parameters. Each of the objects listed above has a Value property that you can set. You can also pass that value into and out of a function. Because you can change the values of the object's members, events produce results consistent with ByRef behavior, even though the event syntax says the parameter is ByVal.
Assigning a value to an argument associated with a ReturnBoolean, ReturnEffect, ReturnInteger, or ReturnString is no different from setting the value of any other argument. For example, if the event syntax indicates a Cancel argument used with the ReturnBoolean object, the statement Cancel=True is still valid, just as it is with other data types.
Читайте также: