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?
I have a list of names in a single cell. They are all seperated by a comma, then a space. Example would be: John Smith, Steve Wilson, Wallace O Malley, etc. What formula could I use to pull out the names individually, starting from the farthest right?
I have a spreadsheet with cognitive tests (approx 300) that have been given names like
w3_th_e w4_th_e dkefs_TMT1_e pegboard_eh_e
What I need is a VBA macro that replaces the _e only at the end of the string to _ss for those strings that begins with w3_, w4_ and DKEFS_ (e.g. from w3_th_e to w3_th_ss)
Likewise I want to replace only the _e at the end of the string on all other names (e.g. pegboard_eh_e) with _t (e.g. from pegboard_eh_e to pegboard_eh_t) Is this possible?
I work with data that varies in row numbers but is consisten in column width. I am trying to write code that will create a named range for the data but be flexible to expand or contract based on the amount of data that is pulled in. Below is the
Const lngLastPossRow As Long = 65536 Dim strDataRng As String strDataRng = ActiveSheet.Name & "!R4C1:R" & Range("a" & lngLastPossRow).End(xlUp).Row & "C17" Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveWorkbook.Names.Add Name:=("Data"), RefersToR1C1:= _ strDataRng
While the result creates a named range called "Data" , it does not allow me to reference and data in any formulas ( sumif's, etc). Can someone tell me what I am missing. It appears to be returning the wrong data type (string as opposed to range values).
I have a list of names in the A column of the sheet. I wish to use the input string, a new name, of a vba box to insert into the column of existing names in alphabetical order by inserting an entire row, which I suspect will be the case anyway. Also 'Return' key doesn't seem to be inserting a CR on the page that I typed this request upon.
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
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
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.
I 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.
I 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.
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 working on a workbook which uses a large number of variables. I am trying to keep them as "local" as possible to keep it simple. Some of my variables are local to the subs they're used in. Some are global as they're used by subs in several sheets. A third type of variable is used by several subs all belonging to the same sheet. Is there a way of declaring them so they're known by all subs in that sheet, but not by every sub in the workbook?
I have the following code in a module that I would like to initiate when the workbook opens:
Code: Private Declare Function GetComputerName Lib "kernel32" _ Alias "GetComputerNameA" ( _ ByVal lpBuffer As String, _ ByRef nSize As Long) As Long
Public Property Get ComputerName() As String
[Code] ........
In a worksheet, I have =compname() in cell A1 and =hdserialnumber() in A2. What is the trick to make the values in these cells appear as soon as the worksheet opens?
I have a series of procedures I am calling from a function that each run fine when executed individually. These procedure are titled: DataValidation, Test1, Test2
The function will not run past Test2- I do not get the msgbox "Stage 2"
Code: Public ArrayD Public ArrayD2 As Variant Public RngD3 As Range
I have a simple/dumb question... How do you "capture" a value that is returned by a custom function.
I have tried searching the forums for this & I know that I should by all rights know how to do this by now... but I just can't figure it out.
Here is a Function that I copied from Ktrasler in this thread: Week Numbers
Public Function MyWeek(DateArg As Date) As Byte
Const BaseDate = "30/12/2001"
DateArg = CDate(DateArg) - (Weekday(DateArg) - 1) MyWeek = Int(((DateArg - CDate(BaseDate)) / 7) Mod 52) If MyWeek = 0 Then MyWeek = 52
End Function
I know how to pass variables to Functions, and tried this one out & it worked splendidly for my needs, but how do I take the value of "MyWeek" and use it in the subroutine that I am calling it from?
trying to get it to put the 2 variables into 2 cells in the worksheet for the moment!
The Forms code is:
Private Sub CmdCancel_Click() Me.Hide Unload frmMsgBox Exit Sub End Sub
Private Sub CmdOK_Click() Me.Hide End Sub
It always fills the cells with False and blank, no matter what buttons are pressed (except cancel), so all I can think is that the information isn't feeding back through properly from the form.
This is the code after editing to make it more clear
Public Sub 1() BookA= activeworkbook. name BookB=Application.Workbooks.Add Workbook(BookB).activate End Sub
Public Sub 2() BookB=Activeworkbook.name
With BookB. sheets(1) .range("A1")=BookA.sheets(1).range("B1") End Sub()
At the end of public sub 1, BookB is the active workbook. What I want to do in public sub 2 is to copy some data from BookA to BookB. Unfortunately, when moving from public sub 1 to public sub 2, BookA needs to be defined again. The code above is the code that I use in my add-ins. I figured out for non add-ins code I can define BookA with thisworkbook.name when BookB is active as I before work with BookA. This does not apply for add-ins as thisworkbook will refer to my add-ins code. Is there anyway of keeping definition of BookA is constant from one public sub to another public sub? This is simplified code. In fact, I can't merge public sub 2 with public sub 1 due to some reason which I don't say it here.
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:
Basically someone has coded a VBA script to pull a users details from AD when a button is clicked, using the staff id as the search criteria.
I've amended the criteria so it uses Environ$(USERNAME) to find details of the current logged on user, but i am struggling to get the resulting information out of the Sub to be used in other Subs! It currently just fills a cell with the required info.
A recent message prompted me to complete a project to show all modules and subroutines in a workbook.
As use of VBA to manipulate the VB Editor is a recurring issue I thought it a good idea to share this.
Option Base 1 Dim WBname As String Dim ws As Worksheet Dim TitleStr As Variant Dim VBProject As Object Dim ToRow As Long Dim ToCol As Integer Dim ComponentType Dim MyComponent As Object Dim ComponentName As String Dim TypeArray As Variant Dim StdCol As Integer Dim LastLine As Long Dim CurrentLineNumber As Long Dim CurrentLineText As String Sub SHOW_ALL_MODULES() WBname = ActiveWorkbook.FullName On Error Resume Next Set ws = ActiveWorkbook.Worksheets("WB Contents") If Err.Number 0 Then ' sheet not exist....................
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.
