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.
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 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 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.
Do we have NumericUpDown Tool Control in MS Excel 2003 that can be employed on a userform. The one similar to the numericupdown of VB.NET. I want to restrict user to input only numerals. Though I can use IsNumeric Validation in Textbox_Change event but still I was curious to know if there is any other in-built function in MS Excel.
View 2 Replies View RelatedI'm trying to return the selected items from a listbox to a range within a Worksheet when the user press the >> button as shown below. The point is i can't return ALL the selected items but only the first picked by the user.
Here is the code i'm using when button >> is pressed:
Code:
Private Sub Inserir_Atributos_na_Analise_Click()
Dim i As Long
Set ws1 = Worksheets("ListBox")
[Code].....
Problem: I have textbox entries that are part of a Userform that opens using a macro on a speadsheet button. Once this form is open, I can no longer actively work in the spreadsheets.
Need: I need a way to minimize or "put on hold" the Userform so that I can freely move around in the spreadsheet. This could be in the form of a button on the Userform. Then, I need a way to bring back this userform to the point I was at before being put on hold so that I can continue to input entries into the textboxes (again, a buttton that could bring it back). There should be a way to toggle between both worlds.
I came across a userform which was perfect for my needs, as I only had to create additional fields, which I did and have attached. However, since I have more than 10 fields, I was getting an error with respect to the listbox function, where it stated that the list property could not be found. I gathered from the searches I did, that this is the max limit for this particular function. I have been unsuccessful in finding a solution, partly because I don't know very much and I'm not understanding how to incorporate some of these solutions into the vba code. I've created simple userforms before, but this one is a little more advanced than my current capabilities.
View 2 Replies View RelatedI'm writing a program on VBA for Excel and I've succeeded in just displaying the Userform when opening the file. I have set the ActiveWindow.Visible option to False and the Application.Visible one to false as well so that i only have the user form displayed.
However, if i try to open an excel file via "My Computer", the file doesn't open. I also noticed that when I was leaving the application.visible option to True, i couldn't access the application while the userform was running. I think i need to do something with an event, but i can't find the event corresponding to opening a file oustide Excel.
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 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.
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 this formula =COUNTA($A:$A)<=4 that limits amount of cells that can be populated in column A, I use data validation with "Allow costume" option and using that formula. It works fine from worksheet it displays the message when the limit is reached but it doesn't work when data is inputed/populated from userfrom, it allows userform to put more entries than set limit 4 in this case.
View 3 Replies View RelatedI 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 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.
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'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 need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
View 4 Replies View RelatedIn a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....
I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.
From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.
Within the 'tools' menu option you have 'Macros'. On a standard tool bar you have the option to record a new macro, edit......, however sometime last week I created a custom button and placed icons on my tool bars that allowed me to click them to record new or run custom made macros. After a clicking the 'custom macro' buttons a few times by mistake, I decided to remove the custom buttons in the hope of returning the macro options (within the 'Tools' menu option) back to normal. However this has not been the case. Now I have no option to record a new macro the the 'tools' menu system.....it has disappeard. Please could someone tell me how I can reset the 'Tools' menu system back to normal?
I am trying to create a simple translation tool for my product catalog, which is in Japanese, to English.
I have the Japanese and the English terms listed in a file (say, File A) in Col A and Col B which will keep increasing every week.
I want to create a macro which goes thru this list and replaces ALL the Japanese terms to English in "ALL THE SHEETS OF THE PRODUCT CATALOG" (say, File B) .
I have created a button in File A so that when I press this button, I browse my computer, select the file (product catalog) to be translated, replace all the Japanese terms to English terms in ALL THE SHEETS and saves it under a different filename.
I'm a big fan of the old crossword these days so i decide that i will try and create a crossword creator in excel!
problem is i'm not sure how to use the Thesaurus tool?
Lets say i have a string in cell A1 how would i modify the string using the thesaurus?
also any idea how i can randomly generate a word?
I have an application that I attached to this post, which I have been working on, for the last four or months, in my free time on the weekends and the last part I need some help with. The doc has been updated with new macros and a chart.
I would like help with a macro that I can activate and cause a message box to appear when I open the excel doc. I would like also to be able to activate the message box and its associated macros after the document has been opened. If you look on Sheet1 of the attachment you’ll get an idea of where I am trying to go with the message box. I would like it to have
- Message “The Current Time is (Macro puts current computer time here)”
- Question “How much time do you have?” then beneath this question
- Blank field labeled Hour to give answer
- Blank field labeled Minute to give answer
Depending on the current time on the computer as well as the hour and minute entered, a macro with a matching title (each macro includes a time, hour, and minute in its Sub name) a code in VBA will be selected that fits that criterion. For example, let’s say it is 5:30am and I entered 1 hr and 15 min in the empty fields of the message box. As a result the macro named “Sub Hour1Min15time530am” will be activated. Also due to the fact that the macros I have are in 15 min intervals the code would need to round to the nearest time, so if it was 5:33am when I ran the code the macro would default to 5:30am whereas if it was 5:39am when I ran the code the macro would default to 5:45am. You’ll see time controls at the bottom of the application. If I can get the code I am looking for those are going to be removed.
I am making a Program search tool for my company. I would like the operator to type a part number into a cell. If the part number is valid (from a master list on a different sheet), then I would like to pull the information from the master list and populate a few cells on the search worksheet.
Example:
An operator types: "W3303-01" in a cell and clicks a button. The macro would populate cells on that worksheet with information from another worksheet that pertains to "W3303-01"
I have 143 macros, but I put 8 of them here b/c the post would not go through with all of them. I couldn't fit all of them on an attachment either b/c the file size was too big. However, I have this goal I have been working on for the last three months on the weekends and the last part.
I would like help with a macro that I can add onto the ones I have. I would like the macro to activate and cause a message box to appear when I open the excel doc. I would like also to be able to activate the message box and its associated macros after the document has been opened.
I would like the message box to have
- Message “The Current Time is (Macro puts current computer time here)”
- Question “How much time do you have?” then beneath this question
- Blank field labeled Hour to give answer
- Blank field labeled Minute to give answer
Depending on the current time on the computer as well as the hour and minute entered, a macro with a matching title from the list below will be selected that fits that criterion. For example, let’s say it is 5:30am and I entered 1 hr and 15 min in the empty fields of the message box. As a result the macro named “Sub Hour1Min15time530am” will be activated. Also due to the fact that the macros I have are in 15 min intervals the code would need to round to the nearest time, so if it was 5:33am when I ran the code the macro would default to 5:30am whereas if it was 5:39am when I ran the code the macro would default to 5:45am.
i have about 500 excel files that are built to specific format, only some data changes.
I need to make same changes to all the 500 files, so i'm asking that is there any kind of tool (free/commecial) that can batch changes to all files?
I want to let my buttons on tool bar in group status, there is a saparator bar between two groups, what can I do? I tried but no any result.
View 2 Replies View RelatedI have 2 sheets, one with users and a tool they have been using, and one with a list of unique users, I would like to count how many unique users are using a tool. The sheets are set up like so,
Sheet 1
User Tool (number of uses)
abcdefg 1
hijklmn 5
1234567 1
1234567, abcdefg, opqurst 2
wxyz123 0
Sheet 2 Have they used the tool
abcdefg
hijklmn
1234567
wxyz123
opqurst
So yes, I am trying to count whether or not the unique users have used the tool, this is a simplifed version as my data set runs into the thousands. I was using the countifs function, but that doesnt count properly if theres more than one user in the group.
I've been look for an excel tool to draw venn diagrams with the 'circle' size in propartion to the set (population) sizes and the correct degree of intersect.
Having no luck I've made one myself - it's not pretty (the code sure is ugly) but it get's the job done.
May not work in versions earlier than xl07 - haven't been able to test
So take a look - pull it to pieces all you want.
**Warning**
Attachment contains Macros
**********
Orginal text in thread:
Silly question..but that's never held me back.