Get Range Specified In Refedit Control
Sep 7, 2007
I have built a user form that amongst it's other functions displays the results from three cells on one worksheet.
The values in these cells need to be pasted in three cells on a different worksheet (cells are selected by user using a "RefEdit" field on the form.
The VBA help files offer a single example of the paste special method (I've proven I can paste to the celss but of course that pastes the formula rather than the value!) that shows it operating on a single worksheet as follows:
With Worksheets("Sheet1")
.Range("C1:C5").Copy
.Range("D1:D5").PasteSpecial _
Operation:=xlPasteSpecialOperationAdd
End With
So, simple question...
How can I use the Paste Special method to paste the values of the cells in cells specified by the user?
View 5 Replies
ADVERTISEMENT
Dec 3, 2007
I'm trying to set up a userform with a refEdit box that allows the user to select a cell range. I then need to assign that cell range to a range variable so the macro can use it. I'm having a problem reconciling the assigned string variable that refEdit returns and the range variable that I need.
So what I'm trying to say is that I can assign the range fine through the Userform, and I can see through the debugging screen that mySampleVariable (dim as a variant) holds a range value of 'Access Dump'!$A$8 (the correct range), but when I try to
Dim myRange As Range
Set myRange = mySampleVariable
The code bugs out because it apparently can't assign a string value to a range variable.
View 4 Replies
View Related
Jul 8, 2009
I am using a refEdit control on a userform to get a range from the user (they use the refEdit control to click on the desired range). My problem is that the user typically has 3 or 4 different workbooks open and if one of them is maximized withing Excel, the user is unable to get to them to select the range (only the maximized workbook is displayed and usable). Is there a way around this?
View 4 Replies
View Related
Jan 23, 2007
I would like to use the RefEdit control in a worksheet in order to select a range of cells and pass its address to a different cell without code.
I thought of using the LinkedCell property of the RefEdit as I used it for a ComboBox.
View 9 Replies
View Related
Feb 23, 2007
I am trying to implement a simple userform using the RefEdit control.
So I have the RefEdit control and an Ok (which has code attached to it) and a Cancel commandbutton.
For some reason, I can't get the focus on the RefEdit control (i.e. when I activate the form, I have to actually click in the RefEdit box before it gets the cursor). Which property sets the focus in this control?
Right now I have the Ok button Default property set to True.
I have a commandbutton on the spreadsheet that activates the userform.
View 9 Replies
View Related
May 24, 2007
I'm finding that my add-ins that worked perfectly on Excel 2003 are not functioning on Excel 2007. One of the main issues seems to be loading any UserForms that use the RefEdit control. I get a message saying:
--------------------------------------------------
Run-time error '459':
Object or class does not support the set of events
--------------------------------------------------
View 5 Replies
View Related
Oct 27, 2008
I have a userform with a 3 refedit controls. Each needs to capture a range from a different workbook (already open - only not active).
Is it possible to configure the refedit to capture a range from another workbook (either thru window menu or ALT+TAB)?
I notice that InputBox type:=8 allows it - I'm a little suprised that default refedit doesn't.
View 9 Replies
View Related
Feb 27, 2013
I have a refedit box in a userform wherin i will be selecting a range and thus getting a value say "$A$1:$A$2"
Now what i need is that it should seperate the value as follows so that they can be used in another place
starting column to a variable x.
starting row to a variable y
similarly
ending column to a variable p
ending row to variable q
Looking for code that if i select more than 1 column in the refedit it should tell me to select a single column and not more.
View 2 Replies
View Related
Jun 13, 2007
When creating a userform at run-time, I can add list boxes, command buttons and all the other controls to a userform with code like this
Set newButton = newForm.Controls.Add("forms.commandbutton.1")
Run-time error '-2147221005 (-7FFBFE0D)':
Could not complete the operation due to error 800401F3.
when I try this line
Set button0 = newForm.Controls.Add("forms.commandbutton.1")
View 9 Replies
View Related
Jul 9, 2009
I sent the attached to a user who said it wouldn't run when the 'Draw Venn' button is click - some sort of RefEdit compile error. I can't replicate the error and can't find what's causing the error.
I had started using RefEdit control but couldn't get it to work the way I wanted, so just went with input boxes. I run this on XL07 SP2, other user has XL03 - not sure if that's relevant to the error they're getting.
View 4 Replies
View Related
Aug 1, 2008
I have a userform where users select a range with a REFEDIT control say:
'Sheet1'!$G$38:$AA$39
I need some VBA code to break this down to the following variables:
a variable giving me the first column i.e. "G"
a variable giving me the last column i.e. "AA"
a variable giving me the first row i.e. "38"
a variable giving me the second row i.e. "39"
If it's easy for you, I also need an error handler to ensure that only two consecutive rows are selected by the user.
View 9 Replies
View Related
Dec 4, 2008
how to work with RefEdit tool within a userform!
eg: say I use RefEdit to select a range and I want that range to be in Bold.
Just give me a small code using RefEdit to perform the above action. I hope then I can use it in a larger way.
View 20 Replies
View Related
Jan 16, 2010
Hi all im having a tough time trying to figure out a way to select a range (which will be the entire row )based on a condition that will be data selected from a combobox and display that range in a userform with spreadsheet contol 9
what i have so far is a worksheet with all the data.
i have a combobox on a userform that is prefilled with data from a specific column
every thing is working except when i select the entire row how do i change that to a range that i can use in the spreadsheet control 9 that is added to the userform.
View 11 Replies
View Related
Sep 12, 2007
I would like to define the range of cells in the sum with a number in another cell. Say I want to sum the past 10 years, I would like the sum definition to be controlled by another cell that includes the number 10.
If I want B1 to be the sum of A1:A10, B2 to be the sum of A2:A11, etc., I would like to be able to control the range of the sum with say, C1, which in this case would contain the number 10.
This would allow me to easily change the range of data I am summing: if I changed C2 to 15, B2 would give me the sum of A1:A15 for example.
View 9 Replies
View Related
Sep 4, 2009
I have a workbook with 20 sheets, including a control sheet 'Validation Table'. By setting the required Background/Font in a cell named hdrDefault on sheet VT, I want to click a button on VT to invoke a macro to visit each sheet and set the named Header range, hdrxxx, to the same Background/Font. Originally had the code in a Private Sub linked to a button event, but this can't break out of the worksheet so have moved the Sub into a Module, and calling from the click event:
Event:
View 3 Replies
View Related
Jun 29, 2012
I have sheet where I need to restrict access to a select group of individuals. I have a formula in a select case statement that runs from the workbook_open event and have hard coded the names of the individuals who can access the file. If they are not on the list, they are prompted for a password. This works OK, but I have sheet that is hidden and want to maintain the list of users there and update the code to loop through the values in a named range. I have a snippet of the current code below.
Code:
Select Case function_name
Case Is = "user1"
Run "enable_access_code"
Case Is = "user2"
Run "enable_access_code"
Case Is = "user3"
Run "enable_access_code"
Case Else
' User not authorized or opened while not on company network
' Prompt for project password
form_Password.Show
End Select
View 2 Replies
View Related
Dec 6, 2006
I have a defined range in my workbook which I have not been able to delete (I think it got there through some malware because I didn't create it!)
The main annoyance is that the name refers to another workbook (to which I don't have access) and to a non-existent range therein (resulting in a #REF! error) - so I get the unwanted Update Links message each time I open the workbook. I have created my own workbook with the same name as the workbook referred to by the name, hoping that I may gain some level of control over this gremlin, but to no avail.
The name is "Flow" immediately followed by a thin-lined empty square (like the ANSI character 042 formatted using Wingdings2 font). It could be a line-feed character.
Whatever it is, I have not been able to delete this range name - either directly through the Insert / Name / Define / Delete commands or by using VBA. Using the menu commands I can select the name, edit it (even add a valid cell reference to the workbook name), and click Add - but that just adds a new name "Flow" without the control character and the original name remains! The original name also remains if I select the name and click Delete.
View 6 Replies
View Related
Jan 11, 2008
the macro simply copies a master sheet, then re-organizes/re-sorts the raw data in the new sheet. At the end, I'm trying to set a print range to 1 page wide and inifinity pages long, figure out where the end of the fourth page is, then reset the print range to accommodate four pages. In case it matters, the macro starts with a form that allows the user to input a company ticker and name used in output.
The problem is that when I use the debugging step function (very tedious), it works perfectly. But when I run it normally, it locks up for about 30 seconds when setting the print range to 1 page wide, and cuts off the print range vertically at just over 2 pages tall. Brief explanation of a couple variables:
ColLetter is a function as follows:
Function ColLetter(ColNumber As Integer) As String
ColLetter = Left(Cells(1, ColNumber).Address(False, False), 1 - (ColNumber > 26))
End Function
ActiveSheet.PageSetup.PrintArea = "$A$1:$" & ColLetter(j + 3) & a + 7 .............................
View 2 Replies
View Related
Aug 8, 2008
I want a control to select a range in a userform I'm creating.
I wonder if we can use the same type of control the wizards use, where you click on the right and choose the range? Alternatively, what's the best option?
View 9 Replies
View Related
Jan 10, 2012
creating 2 fields in my userform that the user can input 2 dates that would serve as the range to set the autofilter. So user inputs field1 = 11/01/2011 and then field2 = 01/31/2012.
I was previously using a month range in my userform, populating from the known months and allowing the user to select a range. Since our data is now spanning a new year, this is no longer possible as I cannot make the combobox range go from 11 - 1 (November to January)
VBA Userform - select months & Months between - Code Included
View 1 Replies
View Related
Apr 3, 2009
I've built the code for a Template form for data entry of a survey. Through Control Toolbox I've created the form and used several textboxes, option buttons, checkboxes and COMBO boxes this one in turn being my trouble.
- Sheet one is called "Data" (this is the place where all inserted information is going to be stored
- Sheet two: "Variables" (here is where I keep the required values for the como boxes - inserted manually apriorely...
Question: What is the code to assing to the combo boxes in order to have the values from the sheet "Variables" of the range A1:A4 - use the comboboxq2 for referee, I'll do the others
P.S. see the code up to now:
Private Sub CommandButtonN_Click()
Dim eRow As Long
Dim ws As Worksheet
Dim inf As WorksheetFunction
Set ws = Worksheets("Data")
Set ws = Worksheets("Variables")
' Find first empty row in database survey
eRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ................
View 9 Replies
View Related
Mar 31, 2007
How do I limit the user from being able to choose only 1 year after they pick the first date on the first command button?
Here is the code on a userform which has a Calendar Control ...
View 9 Replies
View Related
Mar 4, 2014
Is there any way in VBA to refer to a control in its own event procedure without referring to it by name/hard-coding?
It might be clearer to explain by a dummy code example:
[Code] ......
I'm seeking what I would need to replace Line1 with.
View 11 Replies
View Related
Jan 9, 2008
Within the ComboBox properties, is there anyway to control after "enter" his hit, you move to the right instead of down (similar to the edit under Tools/Options)?
View 9 Replies
View Related
Jan 12, 2007
I have created a userform within VBA which has a TreeView Control and a Spreadsheet control on it.
I have populated the TreeView control with data and what I want to be able to do is to drag the nodes off the TreeView control to the spreadsheet control.
I can drag onto a normal worksheet but not onto the spreadsheet control (the no drop mouse pointer keeps showing).
View 4 Replies
View Related
Oct 4, 2007
How do I determine which control the user is currently modifying on a multipage form (either changing, enterying or exiting the specific control). when I use "userform1.activecontrol" i get "multipage1" as the control name but I need the actual control on the specific active multipage. (also the .TABINDEX is for the multipage regardless of the on-page control) I use a generic data-field change SUBroutine so need the control name (and the TABINDEX) to provide my SELECT CASE. (so every fieldname_CHANGE calls the same SUB [with no parameters])
View 3 Replies
View Related
Feb 20, 2009
Can i use for this "live search" in column range any control toolbox or form?
only what i want is during writting name it will be shows the results in any textbox or so.
Control box or form can not be programmed so, that after each character check the range and return the results.
View 5 Replies
View Related
Dec 17, 2013
I have a HUGE dataset (more than 1 million rows). I'm trying to find the optimum cell range size - 10 rows, 100 rows, 682 rows, whatever - for a SUM formula. (The formula would be in every row, looking down at the next n rows).
Is it possible to control the SUM cell range specified in a formula in cell (X) with a value in cell (Y)? In other words, the SUM formula in cell (X) would somehow refer to cell (Y) to determine the SUM cell range.
Ideally, I would like to then use Solver to find the optimal value for cell (Y).
Is this possible with just formulas? Or is VBA required?
View 5 Replies
View Related
Mar 12, 2003
Has anyone out their ever seen an Calendar type of control totally built in an Excel vba UserForm?
My problem that I’ve tried to resolve for some time is utilizing some type of pop-up calendar to eliminate format issues in my published Excel forms. I have tried a number of calendar controls but all have to be registered on the local machine and this cannot be guarantied for every machine.
If someone could direct me to a vba UserForm that has this built in that might do the trick. Or is their another way to deal with this?
View 9 Replies
View Related
Jun 12, 2007
I have 2 macros: 1 controlled by a checkbox activex (PA_03) and the other a combobox (PA_03_rows) in a worksheet. I display the results from PA_03, and the user than then increase or decrease the number by using the combobox. But when I change the value (PA_03_rows.value = x) it causes the macro to jump to the macro.
I don't think it did initially, but it does now. According to another post, I saw it shouldn't do it! Is there something I'm just not seeing here?
Private Sub PA_03_Click()
Dim message, title, default, numberRows
Dim PA_rows As Integer
Application. ScreenUpdating = False
TakeFocusOnClick = False
Worksheets("sheet1").Select
ActiveSheet. Range("a15").Select
If PA_03.Value = True Then Goto Unhide:
If PA_03.Value = False Then Goto Hide:
Unhide:
message = "Enter the number of input rows required (1 to 50)"
title = "Non-Featured Standard Input"
default = "1"
View 4 Replies
View Related
