I have a Sub that calls 8 other subs, can I hide the 8 subs on a users macro list?
I tried making the 8 subs "private subs", and it did hide them, but then my call sub macro didn't work.
Any thoughts?
I have an add-in that I'm passing around to my users, and though it has 8 or 9 subs, it only has 2 that they need to see. However, I'd rather not relegate all of the code to a single module, as there is quite a bit of it. I'm aware that I could change all the subs to functions, but they would still appear in the UDF list, and this would also be confusing to the individual responsible for the upkeep of these macros (and I'd have to rework the code a little bit).
I'm just asking if there is any direct way to do this, before I go to the trouble of making a lesser workaround. Google seems to think no, but "don't show macros in list" isn't a very solid search.
I am trying to add a macro to the ACCT DATA sheet, which calls one of two subs (AddCarriertoChecklist() - or - ClrPolChList() located in seperate modules). The macro should call them depending on whether "X" is entered in a cell in column "E". The issue lies in that I have macros doing a few things to this sheet already, and I am unable to tie this one in.
I have the two subs (AddCarriertoChecklist() - or - ClrPolChList()) working. I just can't seem to get them called. This small bit is how I was attempting to call them.
[Code] ......
Below is how I currently have it tied in with the rest of the code for this sheet:
[Code] .....
What is it that I am missing?
how do i make a number list. i want to start at 8000 then i want excel to auto generate up to 9000. it will take ages to sitt and punch 8000 8001 8002 ......
Next problem
how can i sett that cell A1 is connected with B1. so when i sort by number it follows. Example:
I have 2 sheets with a list of account numbers and values on each (Column A = AccountNumber, Column B = Amount)
I want to combine this on Sheet3 which should include all accounts on the other 2 sheets. Some AccountNumbers only exist on on one of the sheets.
Sheet1
A..100
C..200
E..400
Sheet2
A..200
B..100
D..200
Sheet3
A..100..200
B.....0..100
C..200..0
D.....0..200
E..400..0
I'm trying to have a drop down list in a spreadsheet which combines the info from two columns.
For example:
A B
1 John 29
2 Sandy 40, etc
And the drop down list turns into
John 29
Sandy 40
Is this possible?
I have two columns of data and would like to generate a new list showing unique combinations of those values (see attached sheet).
unique list.xls
I have a global list in B column and I want to create a text about the list. If I put "1" in A column, I want my text to be on B11 for the selected items. So excel should do a loop for the selected range and combine the selected items in a new line and give me as an output.
You may see the picture Untitled 1.jpg
is there a way to combine the info from several columns into 1 list?
All of my columns have information in rows 1-20, but I want it all to automatically combine into a list in column B on a new sheet.
This is Excel 2007
I'd like to combine two VBA subs existing on a single sheet. Since they can't duplicate I need some sort of a switch statement...?
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range, cell As Range
Dim Msg As String
Dim ValidateCode As Variant
Set VRange = Range("E4:E100")
For Each cell In Target
[Code]....
I have two subs which I've been trying to combine into one. Essentially all that is changing is the slicer name and range value.
Here's what I have:
Code:
Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)
Dim cache As Excel.SlicerCache
Set cache = ActiveWorkbook.SlicerCaches("Slicer_Project_Type3")
Dim sItem As Excel.SlicerItem
Dim myString As String
For Each sItem In cache.SlicerItems
If sItem.Selected = True Then
myString = myString & "," & sItem.Name
[code].....
But it's erroring out on me.
I have 2 sub, and I want to use the same variables that reference values on my spreadsheet.
for instance, I declare and set ws as worksheet("sheet1") in the first sub.
in the second one, I also want to reference to the same ws. Instead of having to declare it twice, being redundant, what would be a good way to do this?
How can I have a sub repeat itself? I have a code and I want it to ask the user at the end if they would like to do it again. If so the whole sub needs to be repeated. I'm sure this has something to do with looping, but I don't know how to manage this.
View 3 Replies View RelatedI have a table which has employee names on the left handside. Next to them are contacts in a region.
View 7 Replies View RelatedI'm new to Macros and need your help. I've read online but can't find instructions on completing what I want.
I have a workbook with about 60 tabs that has various numbers of rows. I want a macro that will combine all the rows from each tab onto a new tab.
Is there an easy marco that i can do to acommplish this?
I've been Google-ing for the last 45 minutes and tried to combine them myself, but I'm afraid of breaking it.
I believe the second code was taken from the MrExcel site, I don't remember.
CODE 1:
// Track Changes; updates any/all changed cells with a comment identifying the author, date and change that was made //
Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Select Case Target.Column
Case Is = 2, 4, 6
Target.ClearComments
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised "
CODE 2:
// Whenever a specific range of cells are updated, the adjacent cells update with a timestamp //
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("F3:F1000"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value = Now
End With
End If
I'm having some trouble getting to grips with using/calling variables and other sub routines.
View 9 Replies View RelatedI have created a few User Defined Types in a Main subroutine, but when I pass them into another sub the intelisense for the elements doesn't come up. I can type in the elements manually - and it all seems to work okay - but with a lot of types and elements I really want it to save me the effort.
View 2 Replies View RelatedI think I am confused as to how to declare a variable in one sub and use it as an input into another sub. I have attached some code below that assigns a value to two variables and then calls a sub that uses those values. I am brand new to programming so I guess I am confused how to implement this.
View 3 Replies View RelatedVBA - Exit all Subs not just current
View 2 Replies View RelatedI have some code I'm running:
Code:
Sub Main ()
Dim Value1 as integer
Value1=5
Call firstroutine
Call secondroutine
End Sub
[code].....
Somehow I need to pass Value1 & Value2 to secondroutine.
Calling a Sub from another Sub is quite common, how about a Sub that does nothing else but call all the subs to execute the entire job?
Will the subs run sequentially after one completes, or will there be the likley hood they will bump into each other causing all sorts of chaos.
How can it be that this works?
Sub AA()
Dim ReportFileName As String
Cells(1, 19).Select
ReportFileName = ActiveCell.Value
ChDir "C:"
Workbooks.Open Filename:=ReportFileName _
End Sub
And this doesn't work?
Sub BB()
Dim ReportFileName As String
Cells(1, 19).Select
ReportFileName = ActiveCell.Value
CC
End Sub
Sub CC()
ChDir "C:"
Workbooks.Open Filename:=ReportFileName _
End Sub
Somehow it seems that the value of the string "ReportFileName" is forgotten when running the subroutine CC. How do I make it not "forget" the file name?
The below is a selection of some of the code i have written to organise data. The below works, but i'm not sure how/if I can make all the subs work in order automatically once I start the first Sub? I realise what I have written is probably pretty poor code so many need changed?
Sub move_to_all()
Sheets("All").Select
Cells.Select
Range("A10").Activate
Selection.Copy
Sheets("Rest").Select
Range("A1").Select
ActiveSheet.Paste
End Sub
Sub Omni_Trades().....................
I have Three questions about debugging VBA code of an excel add-in.
-Breakpoints set in certain Public Subroutines are NOT hit. I have an application that writes data to the add-in. You cannot debug the app + the add-in. The add does things(e.g. writes data), then calls a main public macro in the add-in, then the addin takes over. If I comment out the main macro, the parent app halts, then I try to set breaks in the add-in.
After resumeing, some breaks are never hit. I know this because if I set a stack trace (output ing the func name to a temporary worksheet), I see these functions being hit. All the modules are public.
Usually in C, breakpoints are not hit because that code is NOT part of the address space of the program.
2. Very naive question (I am a C,C++ programmer)...some tiimes when I hit F5, the whole app runs, other times a list of macros comes up (just as if I had gone to the menu and went toolsmacros. Why is this?????.....placement of the cursor?????
3. If in the app I have 5 code modules, with two public subroutines each.
HOW COME, IF I GO TOOLSMACROSRUN (same as what I get sometimes when I go F5), I ONLY GET A PARTIAL LISTING OF THE SUBROUTINES, ie NOT ALL 10?
I am still learning and was wondering what is wrong with this. I have cut some of the code out as it pretty long. This is my main procedure:
Public Sub cal_parameters_Click()
'istat(i) = 0 bonded
' = 1 slip
' = 2 soil yielded
Dim istat(1 To 999) As Single
Call secondary_loop 'calls subprocedure secondary_loop
End Sub
It then calls secondary_loop:................
I have 5 macros to be run in sequence. I have copied them into the file attached with ----- line as separator between them.
How can I possibly combine them all into ONE MACRO?
Creating a macro that is a combination of other cell values. the cell order is listed below:
C57 = contains a unique number that will change from time to time eg. 1234
H57 = 1 (this will always be the value of 1)
N57 = contains text eg. AUDUSD
V57 = contains text eg. BS
AB57 = contains a number eg. 1.8954
AH57 = 0 (this will always be the value of 0)
AN57 = contains a number eg. 0.01
AS57 = contains a number eg. 1.7954
AZ57 = contains a number eg. 1.9954
BH57 = conatins either 1 or 0 eg. 1
the cell needs to contain the above examples like this :
BT57 =OpenPosition(1234,1,"AUDUSD","BS",1.8954/0,0,0.01,1.7954,1.9954,1)
This will be needed for a total of 14 rows (57, 59, 61, 63, 65, 67, 69, 71, 73, 75, 77, 79, 81, 83)
The cell with the results cannot have any cell references contained in the end results and must be formatted as above so that the add in program can read and pass on the information.
I have a workbook with two sheets that I need to combine some of the data from. Sheet1 is a template to be used for translation purposes on Sheet2. I would like to put Sales Rep Info in column C on Sheet2 (has only Sales Rep ID) using Sheet1 as a translation table (shows Sales Rep ID and Sales Rep Info). I can do this with a vlookup function but would like to know how to do it in a macro so I can use on other more complex workbooks. Here is a sample data set.
View 3 Replies View RelatedI have an excell spreadhseet that has more than 100 sheets and I would like to combine all these sheets into one master sheet (Sheet1 = MasterSheet) within this workbook. Each sheet has different number of rows used. I just want used ranges to be copied over to a master file appending the previous copied range.
Sub MergeSheets()
Dim strSheet As Object
Dim LR As Long, LC As Long
Sheets("Sheet1").Name = "MasterSheet"
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
For Each strSheet In Sheets
If strSheet.Index 1 Then
[code]....
