VBScript To Call Excel Macro - Loss Of Tab Functionality?
Aug 29, 2013
I am trying to open an Excel Workbook which in turn will automatically run a macro on loading which is a Userform. The Userform will contain 3 boxes, just some simple text boxes.
I don't want the Excel Workbook to show in the background, so I have opted to go for a VBScript to call the Excel Application and open the spreadsheet...
I have a current .vbs script which is as follows...
Code:
Option Explicit
On Error Resume Next
ExcelMacroExample
[Code]....
This works fine, there is no workbook showing and the userform opens as expected. The problem is, I lose the functionality to tab between the boxes. When opening the Userform directly in Excel,
View 1 Replies
ADVERTISEMENT
Aug 2, 2014
I've written a little VBScript that generates several hundred Excel 2013 worksheets. In order to protect the users from themselves I protected certain cells. Everything works as expected, except when I discovered that the password I used to protect the worksheet doesn't work to unprotect it. If it matters, I'm not trying to unprotect programmatically, just opening Excel and going that route.
Here's a snippet of my code:
objWS.protect Password="abc123"
View 2 Replies
View Related
Jun 28, 2012
I'm trying to find out if there is a way to call the security alert to enable a macro. Let me explain...I was working on a sheet with a macro that I hadn't enabled yet. After working for awhile the option to enable it was gone and I found that I had to close the fine and re-open to enable the macro. I'm wondering if there is a ribbon or short cut icon to put in my access toolbar that will give me the option to enable/disable the macro. Seems strange that in 2003 I could to this, but in 2007 I have to close the file.
View 3 Replies
View Related
Apr 27, 2012
Is it possible that if i press tab in excel my active cell offsets by a row and two negative columns.
View 1 Replies
View Related
Nov 8, 2012
I'm looking for the functionality in Excel where you can unhide/hide a group of columns or rows by clicking the plus or minus sign below the ribbon but above the spreadsheet itself. I forget how to do it, but it basically groups the columns together and hides or unhides all of them with one click.
View 3 Replies
View Related
Aug 2, 2008
How do I track my total weight loss in excel? Here is an example of what I am trying to do.
8/1/2008 228lbs
8/2/2008 225lbs
8/3/2008 223lbs
Total 5lbs
I would like use a formula that would subtract my absolute weight loss total day by day in August. Basically, I want the total "cell" to equate to my total weight loss in August. For Example after 8/3/08 the "total cell" should equate to 5 lbs. What formula do I use to do this?
View 6 Replies
View Related
Aug 11, 2009
I am looking for a solution by a macro or a formula to get the number of occurance of a particular word in all the worksheets of a xls.
View 4 Replies
View Related
Oct 15, 2008
I've attached an excel invoice template I've been making for our company. I'd like to protect all the cells except A12:H42, C8, B9, G4 and I44. The problem is, if I protect the worksheet (with these cells unlocked) then it won't let me run the macro (for inserting new rows for extra products).
View 5 Replies
View Related
Jul 10, 2012
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code:
Sub RSLDASHBOARDV2()
'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D.
'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D.
'Do Not Modify Code Unless Given Proper Privileges to do so.
Dim APPSPD As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code:
objField2.PivotItems( _
"TRC").Position = 1
objField.PivotItems( _
"MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code:
Sub STATSPIVOT()
'STATS PAGE BASED ON STATS DATA TAB
Sheets("STATS DATA").Select
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("STATS DATA").Select
Range("A1").Select
[code]...
View 4 Replies
View Related
Aug 13, 2014
This is the code I use to call a macro when the macro Im calling is in the same workbook.
[Code].....
However, I would like to call this same macro when using another workbook. I copied the macro "Clearformating" and pasted into a personal macro workbook module. However when I add this code to the sheet tab it will not run the macro.
I also tried this code.
[Code] .....
View 2 Replies
View Related
May 15, 2012
I'm having some trouble with a little VBScript I have put together to export data into a CSV file.
The VBScript goes through each individual Row in excel and basically exports it to a CSV file in the format I need.
Code:
Dim JobHeader As JobHeader
Dim JobDetails As JobDetails
Dim FileNameStr As String
Dim RowStart As Integer
Dim MsgResponse As Integer
Option Explicit
[Code]....
CloseFile:
Close #1
End Sub
Sub ProcessJobs()
JobDetails.JDShipName = Cells(RowStart, 3).Value 'Col 3
JobDetails.JDStreet = Cells(RowStart, 5).Value 'Col 5
JobDetails.JDBlock = Cells(RowStart, 6).Value 'Col 6
[Code]...
This is working perfectly however there can be multiple lines of different product for the same Order Number and I need to consolidate this into one single line where the JDItemDescription, JDQuantity and JDWeight are updated with the combined data.
I have attached a copy of the Spreadsheet so hopefully you can understand what I am after. [URL]...
View 3 Replies
View Related
Jan 7, 2009
I have a Sheet sheet1 and I want to run a macro when the cell D2 in Sheet1 is equal to 10,7,5,and 3. I only want this macro to run when those values are reached the macro then puts the data onto a sheet called wps. The macro is run as a module and is a sub macro.
View 9 Replies
View Related
Apr 2, 2012
All I am trying to do, is read an Excel file looking for the next cell to update, then update it and ultimately save it.
I cannot get it to work - all the checking says it should and yet it doesn't. I have included my simple code but not being a programmer, I just need this to work. The whole Excel VBSCRIPT is a mystery to me as to when methods etc can be used.
here comes the code
Set objXL = CreateObject("excel.application")
Set wbDest = objXL.Workbooks.Open(pathName & xlFile)
Set wksDest = wbDest.Worksheets("SpeedLog")
xlFindColCount = 1
Do Until wksDest.Cells(1, xlFindColCount).value = ""
xlFindColCount = xlFindColCount + 1
[code].....
View 6 Replies
View Related
Jun 18, 2012
Basically I want to be able to get the names of all sheets in a workbook. I know how to get Excel to print the name of the sheet in a cell once the file is saved, and I know how to hide sheets to make sure that all sheets are referenced in a range. If the name of the worksheet is in the same cell across all sheets (except the ones I don't want included on the totals page), is there a way to call that range and have Excel print off all the worksheet names in different rows or columns via a fill function?
Difficulties: the user may add/delete, rename, and have any number of, worksheets in the workbook. Because these variables are unpredictable, I am having difficulty figuring out the way to do this. Also, the formula should be able to ignore the sheets which have no value in the expected cell.
View 5 Replies
View Related
May 10, 2008
I'm using a VBscript that will copy a worksheet and it works perfectly except that it saves the copy to the same directory as the original workbook. I need it to save to a different directory.
What I believe is the pertinent part of the code that needs adjusting is:
"Input box to name new file
NewName = InputBox("Please Specify the name of your new workbook", "New Copy")
' Save it with the NewName and in the same directory as original
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "" & NewName & ".xls"
ActiveWorkbook.Close SaveChanges:=False "
How would this be changed to specify that "NewName.xls" be saved to: D:/newdirectory/copies
View 9 Replies
View Related
Jun 30, 2009
I have been working on the following worksheet, which has A3-Z27 cells right now and will grow/shrink with time. The spreadsheet shown below is just a portion of the entire worksheet. My vbscript is below the worksheet. I have two worksheets PL Dbase and Waiting List. I am copying columns B thru G from Waiting List if Column J is = to "Yes". To the next available row in worksheet PL Dbase columns B thru G...These columns have the same headings on both worksheets. I am getting the following error, when I click the button I assigned the macro to: Run-Time Error 1004 Copy Method of Range Class Failed.
Waiting List .....
View 11 Replies
View Related
May 21, 2006
I have a work book with 3 sheets. Sheet 1 is the main sheet and sheets 2 and 3 will use (I hope vlookup) to update 3 columns from info in sheet 1. my attempt at a vlookup call in sheet 2 is: =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
my understanding is that: - $b3 is the cell in sheet 2 that will be updated as a result of the vlookup call. - [master_AoJ_2.xls]Sheet1!$B$3;$B$65 refers to range $B$3:$B$65 on sheet1 of master_AoJ_2. it does not work... infact I get nothing at all. when I type the call into cell $b3 of sheet2 excel thinks it is stariaght text. this is how I coded the function call =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
View 4 Replies
View Related
Dec 28, 2007
I have a vbscript that creates a workbook in an existing spreadsheet. I would like to have the script create the spreadsheet if it does not exist. That part is easy enough. The part I don't know how to do is to have the macro that has to run on the sheet created by the script.
View 9 Replies
View Related
Nov 21, 2008
I have a VBScript that pulls SNMP data from some devices. It uses two external EXE's called "SNMPGET.EXE" & "SNMPWALK.EXE". When the script runs, it pulls specific information from our SNMP devices (in this case, I am pulling temperature values from Netboz Devices). The script looks like this :
View 2 Replies
View Related
Feb 23, 2013
I'm trying to call another sub with a variable string. I'm using excel 2007. I get the error "Compile Error: Expected Sub, Function, or Property" on the "Call" code. The want the code to run multiple different subs based on user input. For example if they typed "this" it would run the t sub, then h sub, then I sub and finally the s sub.
Code example
Dim Uncvrtdtxt as string
Dim SubName as string
Dim i as integer
Uncvrtdtxt = "this"
For i =1 to Len(Uncvrtdtxt)
SubName = Mid(Uncvrtdtxt, i, 1) & "Route"
[code]....
View 7 Replies
View Related
Apr 29, 2014
I have UDF and would like to use a Macro Button to have it run whenever I need to. Now I have to disable macros with notification in trust center and after entering data, enable content. I have my UDF in a module, is it possible to have another module with a macro so I can run this UDF whenever I need to?
[Code] .....
View 7 Replies
View Related
Jul 28, 2014
At my new job the CHT/CRT that we receive is three days behind what we are actually using. It makes it hard for me to keep track of my talk time, so I thought I'd use excel to track it. I can see the time I have been on each call through the day. I had to put it in 0:0:0 format to get it to average correctly. (I was trying to get it down to just seconds but that seems a bit complicated. I just then used "average=(a1:a30)" to average them together and it seemed to work a bit.
View 2 Replies
View Related
Feb 7, 2014
I am trying to run different macros by clicking various different buttons on the sheet, I then want different data to load into the user form depending which button was pressed. So I have buttons named "SV_1" and another named "SV_2". when either button is pressed then it runs a common macro that gets the name of the calling item. then I want to add "Macro" to the beginning of the calling item name and then call that macro. here is the code that I am working with, when using a watch i can see the value of the variable is "MacroSV_1" when button 1 is pressed but I cannot get it to run the Sub.
VB:
Public ClkBtn As String
Public CallMacro As String
Sub ItemCall()
[Code] ......
View 5 Replies
View Related
Jun 25, 2014
I am trying to write a sub that executes a sub in an xla add-in. How can that be done in general?
More specifically I am wondering whether it can be done with the limited info that I have about the add-in and the to be executed macro within the add-in.
assume the name of the xla add-in is test.xla. The add-in is locked. I therefore do not know the name of the sub to be executed nor do I know the "on action" name of the sub in the ribbon. All I have is the Ribbon button label. Assume the ribbon button label is "ButtonLabel".
Is it possible to call the macro just by knowing the ribbon button label? If yes, how does the script look like assuming the above xla and button name?
View 5 Replies
View Related
Nov 14, 2008
I've created a simple UserForm, and with some great help from royUK. I've managed to get it working to suite my needs. The next thing I need to do is call it when the user runs the macro.
How do I call the UserForm from within the macro that I created?
Once the user gives the input, how do I take the values and pass them to the loop in the macro?
View 11 Replies
View Related
Sep 11, 2009
I have a macro call psc in one module
in a different module I would like to "call psc"
however, this does not work
View 9 Replies
View Related
Oct 8, 2009
I have created a custom menu and saved it as an .xla, one of the functions on the menu is to open up a workbook from a file path. The second option on the menu is to run a macro from the custom menu that is saved in this opened workbook.
Is it possible to write a code that will go off and find this macro from this open workbook?
The reason I wanted to do this is so the user has two options to enter data from however I didn't want to copy the macro over from the original workbook as in time the workbook will be updated
View 10 Replies
View Related
Mar 13, 2012
I have a named range, which pulls a text value from a vlookup. This text is the name of a vba sub I have written. What I would like to do, is call the macro based on this value. This is what I have so far but I can't get it to run.
Code:
Sub ControlSheets()
ActiveSheet.Calculate
Hideallsheets
Dim MacroSub As String
MacroSub = sheets("Control").Range("SheetMacro").Value
Call MacroSub 'this is the name taken above that i want to call
End Sub
View 2 Replies
View Related
Jul 6, 2009
Any way to actually execute a macro stored as a text file?
Without being long winded - I want to create different macro and physically save them as text files. Once saved - I could call them from a list.
View 9 Replies
View Related
Jun 20, 2006
can i use a hyperlink to call/invoke a macro...?
View 5 Replies
View Related
