i've been searching for nearly two hours and can't find a site with an example of this.
I just want to know the code to create a userform that calls various macros based upon which radio button is selected.
ie. IF number1 selected then call macro1()
IF number2 selected then call macro2()
Is there a way to make a macro fire when you close the VB editor. For example,
I would like one of my pre- recorded macros to start as soon as I close the VB editor.
What i have is a userform which contains textboxes a user can enter or change data in.
What i'd like to do is to have a button called something like "undo last" whereby a user can 'swap' back the last change they made.. does anyone have any idea how i might go about it?
How can I assign 2 different functions on one command button. For example, I want my save button on the form to save the data from the text into a worksheet as well as open a new form.
View 5 Replies View RelatedI have a userform that contains a number of controls. When I press the enter / return key, I would like this to have the same effect as hitting the 'OK' button i.e. run some code. At the moment, hitting enter sets the focus on the next control in the tab order.
View 5 Replies View RelatedI want to do is have a User form that allows a user to add a new column across several sheets (i.e. b2, then c2, then d2) where what the user has selected from 3 defined ranges (in this case commodity, pack_type and pack_spec) is concatenated. see the example attached. So for exmaple, I would like the choice of adding into cell B2 across all sheets (except sheet pack) gold solid 5kg. Then I can add the next into c2. I have tried searching few a few examples of userforms with various problems but I dont know how to code it correctly.
View 4 Replies View Relatedhow to run a userform with a macro that opens the visual basic editor and runs the userform1 in it; rather than using userform1.show.
View 9 Replies View RelatedIs there any way of making the default directory location for application.getopenfilename work properly with shared locations?
Presently I use ChDrive then Chdir but I cant use ChDrive if workbook is opened from a link in email for example. Please see below
I have a VBA routine that asks the user to load an existing file on startup.
I want the window for the file selection to open at the same folder every time. (the folder is on a shared drive)
The workbook to run the code will always be located on this same share at a fixed location.
My present code builds the path to the desired opening folder by collecting the drive letter from the current working directory (which has to be done as everybody maps there shares to different letters here, its not guaranteed to be the same! )
then i ChDrive to this letter.
Then I ChDir to letter plus myKnowndirPath
This works fine, the window always opens at my required folder. But only as long as the user has navigated to the folder containing the spreadsheet and then ran it from there. Because that way a drive letter is available.
However i found that if i sent a link to the workbook or its home folder. And the user opens the link or a windows explorer from that link and then runs file from there.
Then it stops working beacause there is no local drive letter in the working directory path. i.e. working directory shows as
'network \shareddrive
ootafolderanother arget
instead of something like
J:afolderanother arget
I would like to do is develop a stand alone program in Visual Basic ( not in Excel VBA) to update the price file in our Portfolio system automatically using the downloaded Excel format file csv extention file from the BigCharts. But before that, I need to export the *.pri file from our Portfolio system in to Excel which still saves as *.pri extention. Then once it updates, I import the updated *.pri file back in our Portfolio program. I understand that the Excel VBA code can be incorporated in Visual Basic code provided there is an object declaration for Excel file (In this case eventhough the both files are in Excel format, they don't have xls extention). how to use external files and Excel VBA code in Visual Basic?
Below is the code that I currently have in Visual Basic. What I'm trying to accomplish is using the ticker (eg. msft) as a keyword search to look up in the price file. If found , the price of that ticker from the test.csv file will be copied in to the price file which is test.pri. I haven't ran it yet.
Sub UpdatePrice(BigChartPath As String, BigChartName As String, AxysPricePath As String, AxysPriceName As String)
'Below are Excel VBA codes
'Uses the test.csv to look up tickers in test.pri and update the price in it
Dim PriceFile As Workbook, BigChartFile As Workbook
Dim PriceFileSheet As Worksheet, BigChartSheet As Worksheet
Dim MaxRows As Long
Dim PriceFileRow As Long
Dim BigChartRow As Long
Dim BigChartFound As Boolean
Call CheckBookOpen(BigChartPath & BigChartName)
Call CheckBookOpen(AxysPricePath & AxysPriceName)
Set BigChartFile = Workbooks(BigChartName) 'Big Chart website imported CSV file saved as test.csv...............
How to set a password before executing any action.
For example, I have "cancel" button in my userform. If i click "cancel" it unloads the form. Instead of that i need a password prompt. so when i click the "cancel" button ,it should ask for a password. only if i provide the correct password then only the form should be unloaded.
JSON import to Excel - Stack Overflow
I understand most of what this post is saying, but I don't understand how to put it together in an excel module or class module.
I have a database in excel which I want only selected users to have edit rights & others should have readonly rights. I have written the following code where a pop up would appear in selected cells warning user for editing the said cell. What I want is when a user clicks yes he should be able to edit it & when he clicks no the cell should get protected.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim rTriggerCell As Range
If Not Intersect(Target, Range("D1:D100")) Is Nothing Then
Set rTriggerCell = Target
Application.EnableEvents = True
If MsgBox("Edit Cell?", vbYesNo)
End If
On Error Goto 0
Exit Sub
End If
How do you set the code so that, if a condition is met, then several cells are filled with values?
So far, my code is
HTML If Cells(i, 2) < Cells(i, 4) Then
Cells(i, 5) = "A"
Cells(i, 6) = Cells(i, 2)
End If
I am trying to loop through an action within a macro and require some assistance.
I have the following
I have a spreadsheet that when I am running an specific macro and userforms, this one sheet is visible, when the macro is not running, then, the sheet is hidden.
Now, that happens automatically the way that I set up my macro; however, if the macro is aborted, the sheet doesn't hide.
If I press either the "OK" or "Cancel" button without entering any states in (because I changed my mind and want ALL states in there, or I pressed either button by accident), the macro eliminates ALL the rows because it needs criteria.
Can this code be modified so that the message box does the following...
1. If I press the "OK" button but I haven't entered in any criteria in the message box, bring up another message box saying "you have not entered in any states in" which brings up an "OK" box and brings you back to the original message box.
2. If I hit the "Cancel" button, bring up a message box that says "No states will be removed" with an "OK" button, essentially cancelling the "State" macro portion of the sub, but continuing with the rest of the code in the sub if there is any.
I would like to have this message box work this way even if states were entered in the input box but the user pressed the "Cancel" button anyway.
3. Create a NEW button named "Keep ALL States" which essentially does the same thing as the "Cancel" button. (I'm trying to "idiot proof" this).
I have created vba which I want to comment out. I totally stumped how to create this action on vba. My current vba code which I want to comment out using vba
Code:
Option Explicit
Sub YearlyInput()
End Sub
'Speeding Up VBA Code
With Application
[Code] .......
I have a for which consists of
2textbox
1 listbox
1 textbox.
While entering the data, the cursor does not stop in the list box and goes directly goes to the next textbox. Autotab is set to true.
I have a macro that is working fine. It calculates some values based on a SQL database and then copy the values and paste them into another spreadsheet.
However, I need to repeat the same procedure 1296 times.
Please see below the macro.
Sheets("QUERIES").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "2"
Range("C5").Select
Selection.QueryTable.REFRESH BackgroundQuery:=False
Range("D5").Select
Selection.QueryTable.REFRESH BackgroundQuery:=False
Range("C5:G5").Select
Selection.Copy
Sheets("AWB RANGE").Select
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I need to increment the line #3 (ActiveCell.FormulaR1C1 = "2") that should start with 2 and end with 1296.
Also, I need to increment the line #11 (Range("K2").Select) that should start with K2 until K1296.
The other parts of the macro will not change.
Would you be able please to give me a hand on this explaining how to create a counter to start on 2 and stop with 1296 ?
Last friday my personal.xls file went missing. I cannot find it anywhere which means I am missing about 10 key macros. While it is not the upset of the century, my problem is that now when I go into Record Macro and try to "Store New Macro in:" -> "Personal Macro Workbook" and then click OK, Excel brings up a msg box that says:
Personal Macro Workbook in the startup folder must stay open for recording.
Click OK and Excel says:
Unable to Record
I have the following problem to solve:
Cell x in col K is filled with a col number e.g. 'V' (validated via validation list).
Cell x in col L is filled with an amount.
How do I get the amount of cell Lx into cell Vx? This to be done only when the amount Lx not equal to zero.
Can this be done with a formula, or do I have to use a macro? I tried to find the solution for both options, but sofar I have nothing that works. Via the Excel help I could not find the solution. I know how to create a macro activated via a button. I prefer automatic filling by Excel.
(using excel 2k7)
Is there a way to create an in-cell dropdown list, then when only 1 item is selected an action is done, plus this has to be repeated for at least 20 rows, preferably 200.
I can do it with an activeX combo box, but that will be tedious and looks messy. The form control box's were linked, or at least when I created them.
Data validation is the prefered method for my drop down list. It looks neat and is easy to replicate for 200 rows.
What code do I need to have behind a command button to carry out an action (for example select cell A1 or B1) depending on which one of two radio buttons are selected? I have tried:
Sub Button23_Click()
If Opt1 Then
Range("A1").Select
End If
End Sub
I have an excel Workbook made up of sheets that are named after months ("May 2014", "Jun 2014" etc). I want to pull data from each sheet that comes after a user specified date.
View 3 Replies View RelatedI'm trying to prompt user when he tries to delete a value from a cell so he can confirm is action but not the same way to all cells.
Let's say that I have cells A1:F10 with values, and user should be able to change this values but not to delete them (can't be a blank cell) and I want to prompt him that info (something like "Cell can't be blank!").
In other range a want a different thing: let's say cells H1:K20. In this cells user should be able to change cells values and also delete them but I want to ask him for a confirmation (something like "are you sure").
When I execute this, I got error run-time error 445, object does not support this action. I am using window vista, microsoft office 2007. How can i change the code to make it workable?
View 2 Replies View Relatedhow to copy cells from different columns to another workbook and to do this for several lines? The macro stops at the blue line
For i = 8 To WS2.Cells(Rows.Count, 3).End(xlUp).Row
If MySheet.Name = WS2.Cells(i, 3).Value Then
WS2(i, 4).Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Destination = WB1.Worksheets(table(z)).Range("G10")
Anyway, I am basically done with a program which looks for one condition and displays an email to send as a reminder.
What I need is to have an if/then statement which checks a column in the spreadsheet and if the cell in the column states "closed" then an email is NOT displayed/sent. It seems whenever I run the macro, an email appears even if the cell for that row reads "closed."
My if/then statement in question lies following my note " 'here lies my current dilemma to solve"
Code:
Sub ThreeDayEmailTest()
'
' ThreeDayEmailTest Macro
[Code]....
So I have this worksheet full of formulas that I do not want users to accidentally delete. Now, I know I can use the standard "Protect Sheet" option to not trigger any accidental changes but I would like an additional safety check (since management users will have access to unlock the sheet) via a macro to prompt the user with a message box (with Yes / No options) which essentially asks the user if they are sure they want to delete a cell if the "Delete" button is chosen? If the answer is No, nothing will happen. But if the answer is Yes, it will delete the contents in the cell.
View 1 Replies View RelatedIf a name is entered in cboRepName combo box, I want to check if the name matches the list of names in the named range "Names" found in sheet repInformation. If the name is not found, then perform tasks.
Here is what I have so far, of course is not working, but I think I am close.
Code:
Private Sub cboRepName_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim repName As String
Dim nRange As Range
[Code].....