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.
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?
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.
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.
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 RelatedI would like an input box that asks users a select a range of cells....After this certain range of cells is selected....I would than like that range to be copied to a different sheet.
View 9 Replies View RelatedThe code below puts a green border around the cell that is beneath 10 in my chosen range, however I wish to add the border to the row of information instead of just the cell. My columns of data are from columns E to M, but the criteria for whether or not the data gets a green border is in column D....so lets say D15 is less than 10, I would want a border to go around E15:M15.
Sub Test()
For Each c In Range("D2:D350")
If c < 10 Then
c.select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
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.
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")
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.
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.
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.
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.
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
--------------------------------------------------
I have a workbook where data is constantly changing
If two cell values become equal I want to capture and keep the value that was in another constantly changing cell at that time
The IF argument will only caapture the moment and then return to the IF False command
I have excel formula which let for different numbers appear in the same cell,
=IF(OR(F7={10,10.5,11,11.512,12.5,13,13.5,14,14.5,15}),5,IF(OR(F7={15.5,16,16.5,17,17.5,18,18.5,19,19.5,20}),11,IF(OR(F7>=20.5),15,"")))
for example we have AS1 cell in which appear first number 15, then it change to 5, then it change to 15, then it change to 11 etc. What i need is to capture and lock just the first shown number (in this ex. it has to be 15) and put it to another cell (for ex. cell AT1) Maybe there is any formula ?
I have a lot of workbooks with a date as a name (eg. 2004-08.xls or
1999-03.xls). I would like to capture the date portion of this name
with a formula in sheet 'Date' cell 'A4'.
I have streaming data coming into my excel spreadsheet. The data is in 1 cell and its a number that changes almost every second. How can I capture and store this data.
View 1 Replies View Relatedi have a long text in A1 field and i just need to capture some characters in between. Below is the example.
Raw Data:
Cell A1 (r1,c1) = Target: ABC, CustomerOrder, Results: BDE, LastUpdate: 12Dec08
I want to get the Result as below:
Cell A2 = Target: ABC Cell B2 = Results: BDE
Can this be done in Excel Cell format? Or do i need to do it in MS Access?
I am preparing a template on the con-call done with various states. What I want to do is to capture their log in time to the call.
1) As soon as participants log in, we need to input the time of that moment. Click or double click with the cell should give me the current time.
Then;
2) Need to calculate how many participants, logged in to the call On-time by considering the log in time captured.
3) Similarly, we need to count the participants joined within 10 minutes of call and who are late comers.
I would like a formula to capture all values >=50 to <=99 in a row.
View 14 Replies View RelatedI want to extract just the last name from a cell that contains the full name and put just the last into a seperate cell.
WHAT I AM DOING: i copy the contents of a internal screen onto a spreadsheet that i use to check various peices of info and calculations. when i save I like to save my sheet with the last name and then acct number. The internal system screen combines the customers names into a single cell. Right now i have to type the last name into a new cell and have a simple save macro that concatenates that last name with the acct number and saves it into the appropriate folder.
The field i am pulling from is always formatted with FIRST NAME then MI (IF PROVIDED) and then LAST NAME. So when i dump the screen contents into excel A20 may be MIKE SMITH, or MIKE T SMITH. so i need something that looks backwards in the cell and stops at the first space and dumps SMITH into another cell of my choosing (B1 in this case)
In my sheet, i have a list of machines in cells A2 to A5 & list of Plants in cells B2 to B5 as below
Col A..........Col B
Mach.........Plnt
A...............1
B...............2
C...............3
D...............4
What i want to capture is the last machine selected by clicking and also the value of the last plant selected by clicking
I want the machine last selected (clicked) to be in captured in cell D2 & the Plant last selected (clicked) to be in captured in cell D3.
i.e if i click Machine C in column A then go the column B and click Plant 4
Then D2 cell should show C and D3 should show 4
I have a cell (for example, "A1") which is inserted with a WINROS formula to retrieve some data into my spreadsheet. And it is running live at all time.
Actually, I can't think of any formulas to capture the value from cell "A1" to "B1". Because I do not want the value that I captured into cell "B1" running live. I just want the value "B1" fixed after captured.
I have a number result in a cell (d1) from a calcuting formula elswhere on the spreadsheet.
As the worksheet is constantly calculating the result in d1 keeps increasing and decreasing
I would like a formula in cell f3 that will give the result 1 if the d1 has reached the number 45. If cell d1 has not had the number 45 appear f3 will be 0
I need to find a term "aa" and upon finding this term, the term on the right must be "bb" before we capture the number. Output will be 15 for this case.
Note that we do not know which cell "aa" will occur in, but the term "bb" will always be on the right of "aa". Can anybody help me with this? I have attached an example for your reference. Hopfully by modifying the below code, i can get the value i desired.
Sub test2()
Dim xx As Variant
Set rng = Cells.Find(What:="aa" , After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True)
If Not rng Is Nothing Then xx = rng.Offset(0, 3).Value
MsgBox xx
If rng Is Nothing Then xx = 0
MsgBox xx
End Sub
How to modify this code so that I select only one column triggers the time stamp update? For e.g. if i make any changes in column A, the date stamp is updated in the corresponding cell in column B. Basically, I am trying to narrow down to only one cell in the row, but it should work for any row in the sheet.
Refer to the below post: [URL] ....
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 1 Then Cells(Target.Row, "B") = Now()
End Sub
I have a moving average spreadsheet that I am having trouble with. In column F of 'Data for Pivot' Tab, I have a set of data that doesn't begin populating until 101 data points (cell C8 in 'Mov_Avg_Chart' is the input; in this cell F138 of 'Data for Pivot' tab) after data begins populating in Column E of 'Data for Pivot' Tab (in this case data begins populating in Column E in row 38).
What I would like to do is have the data in cells F38 to F137 = the value in cell F138. From there on forward (cells F138 and onwards), I would like the formula to calculate data in the same way as it is currently calculating it. That is, I would like the values in cells F38 to F137 be 0.00110 (in the current example). However, the data will be constantly changing. Thus, sometimes the data in column F will begin at a different cell than cell F138. In essence, I want the formula: when column E begins populating data but column F has not, I want those column F values to equal the value of the first populated value in column F (which is usually numerous cells below this value).
I just can't seem to get this one right after spending considerable time on it. I would appreciate any help you can give on this.
I have a spreadsheet that has two tabs that show different views of my report. In tab "Team View" I'd like to capture the information that is in "Cost Centre View" in the fields Nov09 to Oct10.
I want my formula in "Team View" tab to take the TOTAL "Run Rate Reduction" value in "Cost Centre View" ONLY and have the same "Initiative Type" in "Cost Centre View" that align to what is shown in "Team View" in column A (i.e. ISO).
I tried using a SUMPRODUCT but my formula didn't work.
Is there another formula that will give me this calculation?
I am looking for a formula to capture the "date" of my first sale to new customers.
A B C D E F G H Date of first sale
1 Co. Name 2/2/2012 6/5/2012 7/6/2012 10/16/2012 1/22/2013 4/17/2013
2 ABC Co. $10 $11 $5 $12 $12 2/2/2012
3 Smith Co. $19 1/22/2013
4 Brown Co. $11 $15 6/5/2012
5 AAA Co. $5 $10 7/6/2012