Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.
View 9 RepliesI want to hide and unhide tool / macros menu so that users don't run the macros in the workbooks.
I am totally new to Macros. I need a Macro which should format a column based on the value of another column.
Consider I have 10 rows. I have to format column D, based on the value of Column E. If the value of Column E is > 1000, then the background color of Column D should be changed as green. The most important requirement is Column E should be invisible, Changing the font color of Column E as White does not seem ok cuz when we select the sheet entirely using Ctrl+A, the white values are very much visible. Can this be achieved using a macro?
I am using Reafidy nice bit of code to hide worksheets if the user disables macros. The problem, though, is that it doesn't work for Chart Worksheets. How should I adjust this to accommodate charts?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
bIsClosing = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wsArray() As Variant
Dim iCnt As Integer
Application. ScreenUpdating = 0
Splash.Visible = True
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies View RelatedI have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
What is the commands or script for deleting a macro automatically using
another workbook macro.
i have a workbook that has the following sheets
working sheet
job sheet
receipt of deposit letter
completion sheet
delivery note
delivery note (2)
odd
even
t&t
glass
ggf
i want to hide every sheet except the working sheet.
I have tried this but the macros bring up an error when i run the macro
my macros involve printing certain pages dependng on what button is pressed
i get an error whatever
how do i stop this
I have built a series of macros and want to be able to select from them in a drop down list. I can use a list or combo box to create the drop down list, but I am unsure how to write VBA to read the text and run the correlating macro (name of macro is identical to text shown in drop down list).
If this isn't possible, I would have to create a button for each of my 12 macros. How do I assign a macro to a button?
I have created List box called ListBox10.
Under format control I have put the range of A1:A3.
Cell A1 is text: Order 1
cell A2 is text: Order 2
cell A3 is Text: Order 3
Im trying to trigger different macros from each of the options available in the List Box...here is what I have so far.
Sub ListBox10_Click()
If ListBox10.Text = "Order 1" Then
Application.Run ("Macro1")
If ListBox10.Text = "Order 2" Then
[Code]...
Can seem to get the thing to run getting Block If without end If error.
I have never used or created a Macro before. I have worked out how to record one etc. What I would like to know is, is it possible to design a macro and whenever an excel workbook is opened it will list all the Macro's you have created so that you can just action on any sheet or work book.
View 8 Replies View RelatedI have a list of authors for about 20 papers. These Papers are listed in sequential order from 1999 to 2005.
I want to track the number of similar authors from paper to paper. Thus I am attempting to create a macros that has the Papers listed sequentially when they were published and the authors for each paper, with the authors name in each cell. I want to create a macros that will compare the Author Cells associated to lets say the Fifth Paper with the Author Cells for the Fourth Paper (the Previous Paper). So that if certain authors come up in the Fifth Paper that were in the Fourth Paper, the # will be noted.
I also want this macros to compare all the Author Cells that have appeared in the past with that of the author cells of the (N) paper excluding the (N-1) Paper, and just note the # of occurences. So using the previous example. If we are talking about the Fifth Paper, it will look through Papers #1-#3 for any matching keywords and note the number.
I have attached my spreadsheet to make it more clearer. For some papers there are more than 20 authors as well just to note.
I need to asign macros to a drop down list, but whatever i do I cannot find a way to do it? Can any one point me in the right direction? I am working with a spanish version of excel and my spanish is not good enough for the help menu!
View 2 Replies View RelatedI have a combobox with a list of about forty items. I would like each item on the list to run a different macro if selected. How would I attach the macros to each of the separate items?
View 7 Replies View RelatedNewbie user of VB. I need some assistance on a project. I have a form that uses a validation list ( cell c5) to populate other cells (c6, c7, c8, h5) using Vlookup.
This part works fine. On the list is an "other" choice which I need to initiate a textbox macros which would accept user input and populate the same cells with the entered data once a cmd button is selected. I’ve tried redefining my variables as ranges or strings with the same inconclusive results. The code as it stands looks like this:
Private Sub Worksheet_change(ByVal Target As Range)
Dim Vname As String
If Intersect(Target, Range("c5")) Is Nothing Then
'do nothing
Vname = Target.Value
If Vname = "" Then
'do nothing
If Vname = "Other" Then Newvendin.Show
End If
End If
End Sub
Using this code doesn’t initiate the event.
Newvendin is the userform to enter new data only if “other” is selected. CmdEnter is the enter button on the userform with textboxes in it. I’m having a bit of trouble with it’s code as well.
Private Sub cmdEnter_Click()
Dim ws As Worksheet
Set ws = Worksheets("OSP Parts List")
ws.Cells("c5").Value = Me.Vname.Value
ws.Cells("c6").Value = Me.Vadd1.Value
ws.Cells("c7").Value = Me.Vadd2.Value
ws.Cells("c8").Value = Me.Vcont.Value
ws.Cells("h5").Value = Me.Vphone.Value
End Sub
The Vxxxxx are the textboxes.
I get type mismatch errors using this code.
I would like it to populate the value typed into the textboxes into the indicated cells.
Final question and it’s probably stupid. If you have more than one Private Sub Worksheet_change(ByVal Target As Range) sub on your worksheet, how do you change the wording on each so that it won’t cause an Ambiguous name detected error?
"I have 2 listboxes 1st listbox is listing all macros in sheet user can select macro which he intend to run and transfer them into another listbox
once he have selected all macros he want to run just press commanad but to run
it will pick all macros from listbox 2 and strat running them one by one"
I have an excel sheet with 25,000+ emails in column A with hyperlinked emails addresses, Column B has different Subject lines based on the category the clients belongs to but on the same sheet, Column C has the following hyperlink :
=HYPERLINK("mailto:"&A2&"?subject="&B2,"Email")
To create a hyperlink of the email address with the desired subject line.
macros that i could use to open about 25/50 outlook message with the email ids in column A of the sheet and the subject lines in column B.
I don't want the messages to be sent automatically but i want to have the option to change the body of the email if necessary.
I am designing (badly!) an excel file that is intended to be as user friendly as possible. I would like to add in a navigation feature using a list box or other appropriate control form that will take the user to the correct tab in the workbook depending on which item they choose from the list.
My aim is too hide all the tabs at the bottom whilst not crowding my page with multiple command buttons, and still be able to navigate easily through the workbook.
I have 6 macros recorded and I need to create a drop down list of macros so that I know what kind of Macro I am using.
View 9 Replies View RelatedI have been searching through the site trying to find a solution to my issue, but I just can't seen to fixt it. Here is the scenario. I am working on a spreadsheet that will read some data, use that data to update the main chart and fill in the information in a table next to that chart. I recoreded 22 macros, total. Each macro corresponds to the especific type of chart that I need/want.
I want to be able to click on a drop down menu above the chart which will have each of the 22 options. Once the user clicks on the desired option, the specific macro will run and do that it should do. When I run the individual macro (without the VB that I wrote and will mention below), the system does what it should do. Everything looks dandy. But then I added this VB code to the sheet where the chart is:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Range("A12").Value
Case "1"
Call a
Case "2"
Call b
Case "3"
Call cc
Case "4"
Call d
Case "5"
Call e................
I have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)...
For k = 1 To numrows
Selection.EntireRow.Insert
Next k
Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...
For l = lastrow To 12 Step -1
If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then
Rows(l & ":" & l).Select
Selection.Delete Shift:=xlUp
edelrows = edelrows + 1
End If
Next l
Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.
how to use excel 4 macros. I do not even know where to put them. I have inherited some spreadsheets that have both Xcel 4 Macros and vba macros in them. (Well, I get a warning that I cannot switch them off when I disable macros on opening a workbook). I cannot even find the offending version 4 macros so that I can re-code them in VBA.
how do I install a macro from my laptop to another?
View 9 Replies View RelatedI'd like to be able to just open the spreadsheet up and have the macros already saved in there. Is there a way to save these macros?
View 2 Replies View RelatedI am currently volunteering for a small organisation. I have spent the last week designing a userform to be used by other staff members in their office. However, when I asked the manager 'Do you have Microsoft Office?', I got a very firm and definite 'yes' in response.
It turns out they are using LibreOffice, which is a free alternative. I have also tried to get my userform to work with Open Office - but cannot seem to get either to work.
I have changed the settings in both suites of software to 'Load/Save Basic Code' and enabled macros to run via the security options.
My userform is not that complex. I have limited knowledge of VBA and zero experience using Basic, so have come up stuck.
I need to write a macro whereby it queries a cell and if there is any value (it will be either blank or contain characters) in the cell a new row is to be inserted directly above the cell.
i have a matrix with all possible values.
i need to develop a macro, which will change the color of the cell according to the value of the cell.
for example if a specific cell in the matrix has a negative value then the macro should change the color of the cell to blue. if its value is positive then the macro should change the color of the respective cell to red.
I have an API that continuously gets stock price. The price appears in one cell at all times - let's call it A1
I want to record the value in a different cell every time it changes, so I can analyze the data.
so A1 = 15
B1 = 15
little later
A1 = 15.2
B2 = 15.2
I did some googling and came to a conclusion that I needed to write a macro.
I see that there is a function or event called Worksheet_Calculate()
Apparently it is supposed to do whatever when a value changes (per calculation)
How do I write a macro, and then execute it?
I've created an Add-in (.xlam file) in Excel 2007 and installed and activated it (it shows up under "Active Application Add-ins" on the Add-in menu for new workbooks).
I can see the macros and code when I go to the VBA editor, but the macros don't show up in the regular macro list.
I know that I can write code for the new workbook that will reference the Add-in code, but I want other users to be able to install the add-in and run the macros in it right away without any knowledge of VBA. Is there a way to make this possible?
I have created a excel spread sheet to be set up like a schedule, including multiple macros through the sheet, when I started it put the macros into "Module 1", now if I wanted to use this same worksheet and copy it and create multiple sheets in one file via the tabs does it make a difference where the macros reside? if it remains in "Module 1" it will only be in one spot will this create problems later on or should the each reside in the sheet objects.
View 9 Replies View Related