Returning Workbook/sheet Through Property Is Failing
Mar 27, 2007
I made a new class to make other code less redundant, but it isn't functioning. The class has a "workbook" and "worksheet" member, and these can be accessed through properties. The problem is that the properties don't actually seem to return anything, and no data can be accessed through them.
Private mActiveWorkBook As WorkBook
Private mActiveWorkSheet As Worksheet
Property Get wSheet() As Worksheet
wSheet = mActiveWorkSheet
End Property
Property Get wBook() As WorkBook
wBook = mActiveWorkBook
End Property
'Sets active WorkBook
Sub SetActiveWorkBook(ByVal wBook As String)
Set mActiveWorkBook = Workbooks(wBook)
End Sub
'Sets the activeWorksheet in the workbook.
Sub SetActiveWorkSheet(ByVal wSheet As String)
If mActiveWorkBook Is Nothing Then
MsgBox ("Invalid WorkSheet selection - WorkBook not defined")
Return
End If
Set mActiveWorkSheet = mActiveWorkBook.Worksheets(wSheet)
End Sub
This class is used in the macro as such:
Dim uDate As Updater
Set uDate = New Updater
uDate.SetActiveWorkBook ("Book1.xls")
uDate.SetActiveWorkSheet ("TestTab")
'Below Code is not functioning
uDate.wSheet. Range("A1").Value = "foo"
' Expected result - set Cell A1 in sheet testTab = "foo"
' Actual Result - nothing
Dim st As String
st = uDate.wSheet.Range("B5").Value
MsgBox (st)
' Expected results - bring msgBox of values of cell B5, this cell is not empty
' Actual Result - Empty Message box comes up
So - its fairly obvious to me that something is wrong with the properties. The members themselves are not null, I have verified in the debugger that the class members refer to actual sheets/workbooks, but the properties don't like passing anything out and show as "variable not set" in the debugger. How can I get this to work?? It works fine when I do not use the class, like such:
Dim wB As WorkBook
Set wB = Workbooks("Book1.xls")
Dim wS As Worksheet
Set wS = Worksheets("TestTab")
Dim st As String
st = wS.Range("B5").Value
msgBox(st)
But I would like to get the class and properties to work to save clutter elsewhere.
View 6 Replies
ADVERTISEMENT
Jan 5, 2010
I am trying to use the Cells Property in VBA but it is not working and I cannot figure out why for the life of me. Below is my ....
View 9 Replies
View Related
Sep 3, 2007
I have a protected worksheet with some macros running on it, and I have been requested to make the workbook shared. Trying to implement this, I keep getting the standard "1004 You cannot use this command on a protected sheet" error when applying an autofilter.
Here is what is making this problem a little tricky:
- As the workbook is shared, I cannot do Protect UserInterfaceOnly because you can't change the protection settings on a shared workbook without unsharing it. Needless to say, I cannot unshare the workbook.
- When I protect the sheet, I do allow the user to use autofilter. Consequently, Sheet.Protection.AllowFiltering and Sheet.EnableAutofilter both are on. Still, I keep getting the error.
The failing line of code is:
shData. Range("_filterDataBase").AutoFilter lngField, strArg
where shData is the codename of the sheet, lngField is the number of the field and strArg is the filtering criteria.
My guess is, that the error comes from the fact that (for some silly reason) in VBA the .AutoFilter method is used to create a new filter, not only to use an existing one. The members of Sheet.AutoFilter.Filters collection are read-only, and cannot be used to modify the existing filter.
View 3 Replies
View Related
Jun 26, 2007
The below code works well if it is on the proper sheet, if the active sheet is not active then it errors- Why? I think I have properly qualified the path to the desired sheet I wish to search on.
Error msg: Activate method of Range class failed
And how do I handle this?
Set Ws = Workbooks("ViewRenameDeleteFiles.xls").Sheets("Item Record List")
What = InputBox("Enter the Name You are Searching for its Record#", "Item Name Searching On")
Ws.Cells.Find(What:=What, After:=ActiveCell, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
View 9 Replies
View Related
Apr 6, 2007
I`m building a macro for which I would like to save a parameter for the macro to read and set.
I would like this parameter to be saved (permanently) in something like a custom workbook property, i.e. Tarabo (=Yes/No).
How can I create/read/wright using vba code?
View 9 Replies
View Related
Jan 23, 2007
TotHCInv.Value = WorksheetFunction. Sum(KRInv, PBLInv, CRInv, PVInv)
If i >= 34 Then CPSCtphRMA.Value = WorksheetFunction.Average("G" & (i - 30) & ":G" & i)
The first line runs properly, but the second line bugs out with the error message "Unable to get the Average property of the WorksheetFunction class". I can simply do the math, but I thought that using the worksheet function would be easier than summing and dividing. I'm curious, though, as to why I can't seem to use the Average function.
View 3 Replies
View Related
Dec 26, 2013
I am having problems with some vba codes when I protect my workbook; 'I get an run-time error 1004 Unable to set visible property of the worksheet class'
The code I am trying is:
Sub Stats1_Return_TextBox1_Click()
ActiveSheet.Unprotect "meme"
Application.Goto Worksheets("BCM Database").Range("L15")
Sheets("Stats 1").Visible = False
ActiveSheet.Protect "meme"
End Sub
How to sort this. One more question is there away of protecting you vba code? stop users being able to view or edit them for instance?
View 4 Replies
View Related
Mar 13, 2013
I would like to know how to set default property of protect sheet tool to check the desired boxes. This because I have written the vba code to unprotect the sheet for some purpose and then protect back but after that there are only 2 boxes that ticked. I want to have another boxes to be ticked too.
View 6 Replies
View Related
Mar 11, 2013
Possible to look up a value from one excel sheet in another sheet and then return multiple values in the first sheet
For example the following is a sample of sheet 1
NSV Code
Item
12
OLANZAPINE
54
ROPINIROLE
And the following a sample of sheet 2 :
Nsvcode
Item
Division
Speciality
Qty
12
OLANZAPINE
CLINICAL
CS
10
[Code] .....
I want to lookUp the NSVCode from sheet 1 in sheet 2 and return the speciality and qty values in sheet 1 as sheet 2 contains a lot of info I do not require and sheet one also contains various pricing calculations I also require.
View 7 Replies
View Related
May 3, 2012
This first macro works fine:
Sub UnhideHBPorCholesterolQ1toQ3()
'
ActiveSheet.Unprotect "password"
Rows("58:67").Select
[Code]....
why the second macro does not seem to work. I get a Run-time error that says "Unable to set the Hidden property of the Range Class".
View 1 Replies
View Related
Jun 9, 2006
I have a form with several combo boxes, and they function just the way I like as far as being able to pick from the list, or typing in them and having it show you the next available item in the list as you add letters. Whats happening that I would like to know how to deal with is... as soon as you type a letter that is not in my lookup range it generates an error. "Could not get the list property - Invalid property array index". I don't want people to be able to add to the list, but I would like a msgbox to pop up. Then allow them to go back to the box and try again.
View 2 Replies
View Related
Jul 20, 2014
I've created a chart in sheet 1 in a workbook. I want to copy and paste that chart into another sheet (lets take sheet 2) of the same workbook. I am using Excel 2010 version.
Whenever, I try to copy a graph and want to do "Paste Special as Link picture". The problem I am facing as "Paste Link" option is inactive.
I am attaching the Excel for your reference.
View 2 Replies
View Related
Jun 21, 2008
i'm trying to copy a figure from cell C12 on sheet 1 into a list on sheet 2.
i manually adjust the figure on sheet 1 and i want it to create a list on sheet 2.
Its to record a series of payments.
i have tried to use the code
View 9 Replies
View Related
Oct 11, 2006
I have a workbook that has several macros. When the workbook is saved, it cycles through and kicks off several of them.
This works fine, no errors. I have another macro that saves and then emails the workbook. When this macro runs, the same set of macros is launched due to the save method however it errors during one of the macros.
Basically what's happening is that the one macro (derivecountry) unprotects a sheet and inputs a data value. This macro works fine when run independantly but when it runs as a result of the email macr, the sheet does not get unprotected and the error results.
Here is the code line that runs to unprotect but doesn't always work
ThisWorkbook.Sheets(strShtName).Activate
ActiveSheet.Unprotect (strPassword)
I've also tried
ActiveSheet.Unprotect password:=strPassword
View 4 Replies
View Related
Oct 9, 2009
My SUMIF function only works when the workbook that I'm referencing to is open. When it's closed and I open the file it shows #VALUE!. How do I fix this problem?
View 2 Replies
View Related
Mar 9, 2009
From this forum I've borrowed code and reused in my own CapEx approval form. The form is originated and goes through three people for approval. Everything seems to work ok on the form but it always forwards to the first approver rather than the next in sequence. I cannot see where the code is wrong.
View 2 Replies
View Related
Jun 16, 2009
i run this macro but it fails
Can anbody tell me why please?
And how can i correct it? .....
View 6 Replies
View Related
May 14, 2013
This macro should copy all data from all worksheets and past them into the sheet named "Consol" however It is not looping and only pastes the one sheet.
Sub LoopThroughSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Formula
FinalRow = Range("A65536").End(xlUp).Row
Range("A2:U" & FinalRow).Copy
[code]....
View 6 Replies
View Related
Feb 9, 2007
I am unable to create a macro and later go back and assign a shortcut key and run the macro.
View 9 Replies
View Related
Mar 8, 2007
I have a macro that selects a specific worksheet and checks for a value in a specific cell when they save the workbook. I would like to have it return to whatever worksheet that was active when they clicked on save. Is there a way to do this without knowing which sheet in the spreadsheet they will be on?
View 7 Replies
View Related
Jan 28, 2009
I have an Excel 2007 workbook with over a dozen user forms and plenty of code that I have mainly obtained from the web and tweaked to suit my needs. I am a novice with VBA, so not really up to writing anything but quite simple code.
My issue is I have some code set to fire on Workbook_Open that will save a versioned copy of the workbook, to the same network folder that the original resides in, with an incrementally increasing file name. the file is stored on a network, but access should not be an issue as I have full access and have no problem saving to this folder, also the event works fine up until I shut down Excel. I have also tried saving to My Documents to avoid the network issue, same result, worked fine as long as Excel is not closed, fails if I do.
It all works fine until I close down excel completely and re-open, where it then fails to work. There are other events happening in the same Workbook_Open sub that still work fine each time, so the sub is firing on open, but this one event fails. I get no error message at all, just no new file copy created.
The workbook is essentially doing the job of an Access database (I know even less about Access), I have a user form as a main menu and various other forms for various data entry and reporting tasks. I am exiting the w/book via a cmd button on the main menu (I've deliberately restricted users control, as many are not very pc literate). I have conducted numerous trials consisting of running the code from the VBA window, closing w/book via cmd button WITHOUT accepting the std save option and re-opening from Explorer window, closing w/book via cmd button WITH accepting the std save option and re-opening via Explorer and all worked perfectly over many sample runs. But when I closed Excel totally (Not just the w/book), created a desktop shortcut and opened from there, that line of code just doesn't seem to do anything, no error or hang or anything. The only way I can get it to function again, is to re-save the w/book (As either a new file with code edited to suit, or overwrite the original), and keep Excel open whilst only closing this w/book. It then functions perfectly again on opening.
I have enclosed below the Workbook_Open sub and some other subs that append to a user log on opening, these work fine all the time. I enclose the others in case they may have some bearing, as they are also fired from the Workbook_Open sub and show no issues at all.
Any suggestions gratefully accepted as I am struggling. As mentioned above, the ONLY part of the Workbook_Open sub that fails is the line "ThisWorkbook.SaveCopyAs newFileName". I have even added "MsgBox "The new FileName is: " & newFileName" immediately after it, and that displays new filename correctly. It seems to me to be hingeing around the SaveCopyAs event, but I don't want to assume that, being the novice I am.
View 12 Replies
View Related
May 24, 2007
I'm finding that my add-ins that worked perfectly on Excel 2003 are not functioning on Excel 2007. One of the main issues seems to be loading any UserForms that use the RefEdit control. I get a message saying:
--------------------------------------------------
Run-time error '459':
Object or class does not support the set of events
--------------------------------------------------
View 5 Replies
View Related
Nov 18, 2009
I have the the need to show the sum of the product of sheet 2 on sheet 1 if several conditions are met.
The formula is working except for the first array:
=SUMPRODUCT(--(Bid_Circuits=$A2),--(Bid_Week_End=MONTH(D2)),--(Bid_Week_End=YEAR(D2)),--(Bid_Completed))
When I use XL's evaluate feature, XL seems to find the proper data yet returns #VALUE!
View 14 Replies
View Related
Jun 3, 2008
I have written the following code that is supposed to be running on opening a work book. The macro is being called via Application.Run "CreatePT" which is in the this workbook code in a Sub workbook_open().
The code run on its own is fine with no errors... It only bombs out when its called via application.run then its a run time error 1004? ...
View 9 Replies
View Related
Jun 21, 2006
I have the following code which works successfully on my Excel 2002 PC and my Excel 2003 PC.
However when I take it to a clients machine it fails...
View 9 Replies
View Related
Apr 3, 2008
I have a dynamic named range in my worksheet:
Range Name = AssignDt
RefersTo: =OFFSET('Raw Data'!$W$2,0,0, COUNTA('Raw Data'!$A:$A)-1,1)
I perform a routine in VBA which deletes unwanted rows of data from my worksheet. The problem occurs if row 2 happens to be one of those rows. It not only deletes Row 2, but it also deletes my Named Range.
View 7 Replies
View Related
Jul 1, 2014
The two sheets are...
1. Sheet1.xlsx (regular excel sheet as the extension is .xlsx)
2. Consolidated.xlsm (macro enabled sheet as its extension is .xlsm)
1. Copy both the files on your system under a particular folder.
2. Now make 9 replica's of Sheet1.xlsx and name them as Sheet2, Sheet3, Sheet4, .......... Sheet10
3. Open the sheet Consolidated.xlsm and see the button i have added called "Pull Data".
Now write down the code by adding a Module in that excel sheet from the code window.
4. Write a code that will open each of these sheets (Sheet1, Sheet2, Sheet3, Sheet4, .......... so on) one at a time and will copy the data from these sheets to the Consolidated.xlsm sheet.
5. Make sure that the data is appended(and not overwritten) from the multiple sheets (Sheet1, Sheet2, Sheet3). that is once you paste the data from sheet1 to Consolidated.xlsm then the Sheet2 data will be pasted at the end and after that sheet3 data will be pasted and so on..
6. In this way at the end we will have all the data from Sheet1, Sheet2, Sheet3...in the consolidated sheet.
7. The Division column in the Consolidated sheet will have the value of first row in these multiple sheets. So after making the replicas of Sheet1, please change the value in first row (Range A1) to any other value to avoid the confusion.
8. I have highlighted the data for two sheets in yellow and grey color in the consolidated sheet.
View 8 Replies
View Related
Feb 25, 2009
how to return data to a second sheet if data selected via a filter.
i have attached spreadsheet i am working on and looking to make this as easy as possible
on sheet "data" cell f2 im looking to return all goals scored by hull
on sheet "data" cell f3 im looking to return all goals conceded by hull
on sheet "data" cell f5 im looking to return all goals scored by hull at home this i will need to do by selecting hull in "home team" filter hull
on sheet "data" cell f6 im looking to return all goals conceded by hull at home this i will need to do by selecting hull in "home team" filter hull
on sheet "data" cell f8 im looking to return hulls last 6 games as over and under which in this example will return the last 6 rows 27-32 and will read 4 under 2 over
on sheet "data" cell f9 im looking to return hulls last 6 games at home as over and under which in this example will return the last 6 rows 22/23/26/27/29/32 and will read 3 under 3 over
View 10 Replies
View Related
Jun 12, 2014
I am trying to compare to values that a user inputs in a user form. It's ok if they are not the same I just want the user to know it. I am only including the part where the if statement is failing:
VB:
If Me.VSLongQuant.Value <> Me.VSShortQuant.Value Then
MsgBox VerUnBal
If VerUnBal = vbNo Then
Exit Sub
End If
End If
I have VerUnBal as Long and defined as:
VB:
VerUnBal = MsgBox("The Long and Short option Qunatities are not equal." & Chr(13) & _
"Is this intended to be an unbalanced vertical spread?", vbYesNo, _
"Unbalanced Spread Alert")
I can provide more info as necessary. There are two problems 1) the message box is appearing whether the numbers are equal or not, and 2) clicking No in the message box does not cause the Sub to exit.
View 6 Replies
View Related
Mar 29, 2012
I have in my 'Daily' sheet I9 which needs a formula to look at the date in C9 and the scan range 'Weeks' F6:AN41 and find the cell with the matching date and then whatever text is in Row 2 of the column the date was in will be returned.
View 2 Replies
View Related