I have this textbox class which I want to show a userform when clicked and prevent manual input.
[Code] .....
I would expect that I could also set some object properties like color, width, height, locked etc. in the class module.
However I can't find how to (seen all corners of the internet). How do I set these properties?
i've reached a point where i would like to learn more about class modules but i have no resources. When i create a Class object can i visually see it? i want to know as i'm looking to use them within userforms if possible
View 4 Replies View RelatedAfter much online digging, and experimentation, I think I determined earlier that you can't really expose an array all-at-once if it is an object/class property, as you can with an array variable. Now I'd like to confirm this, if I could, because it would be neater to run my simulation in an object instead of a giant and labrynthine user-defined type.
I try the following ....
to locate/display a help file, to a far right position with 80% height and 20% width.
Where do I find the property commands to do this? (with values explanations)
or can someone point me in the right direction.
Current working code, but displays centre...
Private Sub CB_HelpMain_Click()
Set Module1.HELPFILE = CreateObject("InternetExplorer.Application")
Module1.HELPFILE.Navigate "c: empsample_htmlfile.html"
Module1.HELPFILE.Visible = True
End Sub
The error I am getting (highlighted in red) is that it cannot view the object properties. So the picture is put in, but it's emplacement cannot be set and the picture cannot be renamed (which must occur for other macros to see it).
Pages are not protected. The only self automated macro is on workbook open, and it only resizes to view the area depending on the persons resolution.
Microsoft Excel 2003
some of your favorite Methods/Properties exposed by the Range Object. How's bout some that you just can't live without?
Have an tricks for example that you can do with XLS03 that for example couldn't do with XLS2K or perhaps XLS07 that you couldn't do with XLS03?
Split from Determine Order Of Event Procedures
I realise that there's no set order for different types of event, because, as you say, it basically depends on what the user does. But what I'd like to control is what order like events fire in.
So let's say that object_1 handles the aplApp_WorkbookNewSheet event (an application-wide event).
Let's say that object_2 is an object of the same type as object_1, and therefore also handles the aplApp_WorkbookNewSheet event.
The objects are entirely independent; they know nothing about each other. However, I would like to be able to control whether the aplApp_WorkbookNewSheet event is fired first in object_1 or first in object_2 when the user triggers this event by adding a new worksheet somewhere.
Error 1004 on the 5th "With Selection" Chunk, .LineStyle part.
What I did is recorded the formatting I wanted on a selected region, then changed the major references to the defined region in my code called rngATotals.
At first I got errors with the "Select" method of the "Range" class with my rngATotals line (the first one). But then I clarified public variables and now its in the formatting chunk that was recorded.
I've tried:
--Changing security/protection settings
--Clarifying my rngATotals references
--Making the sheet containing rngATotals visible (still in my code snippet)
--And not using rngATotals to define my range but more specific, clunky references like Range ("A3":"B7") or Range(Cells...,Cells...) or Range ("RAnge'sName")
What further could I clarify? And why does it have a problem with the 5th borders/linestyle reference and not the previous ones?
I hate to have to ask another question about this error because there are so many...
Is there anything beyond what I've tried that is a usual way to approach this problem?
Anyway heres the
Sub FormatTotalsTables()
'
' FormatTotalsTables Macro
' Macro recorded 7/22/2007 by AEB
SumSheet.Visible = True
rngATotals.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
I've created a class called CStock. It has 5 standard properties (with Get/Let methods) as explained in that article above. Nothing fancy here.
I also want to create a CPortfolio class that has 3 properties: pName, pNumberofPositions, and pHoldings. the first 2 are stardard, but the last one is different. I want it to somehow keep a collection of stocks.
I've created two modules, card and pack. Card has three variables (value, name, suit) and pack is made up of an array of 52 cards. Referencing it from a test module (NOT a class module) I expected to be able to use debug.print pack.card(32).suit to return the suit of the 32nd card. Instead I get an error message "Object or With variable not set". What am I doing wrong
Private p_strSuit As String
Private p_strName As String
Private p_iValue As Integer
Public Property Let Suit(strSuit As String)
p_strSuit = strSuit
End Property
Public Property Get Suit() As String
Suit = p_strSuit
End Property
Public Property Let Name(strName As String)
p_strName = strName
End Property......................................
I'm getting the following error:
"Object library invalid or contains references to object definitions that could not be found"
I wasn't getting that error last night and I'm not sure what I may have done to cause this error.
It seems to be cause by code running on one sheet of my workbook, but I'm not really sure about that. I'm still a bit of a novice at VBA.
I'm using Excel 2002 SP3 and I'm running MS XP Home as my OS.
Do you have any ideas what can cause this error and/or how to trace down the offending objects/code?
storedPath = .CustomDocumentProperties("PathCertString").Value
Although the question I'm about to ask is not related to Excel, but related to MS Word, the coding is similar.
The above code I used to set the properties value, but I get an error highlighting 'storedPath'. I speculate MS Word does not recognized this word. Is there another word or code that I can use to set the value in the MS Word document properties?
Within my code I have restricted the toolbar options that a user can access (i.e. for Menu Option 'Edit''Tools'):
Set myCmd = CommandBars("Worksheet menu bar").Controls("Edit")
myCmd.Controls("Delete Sheet").Enabled = False
But if the user wishes to delete the sheet, they can select the specific WorkSheet 'Tab' and Right-Click to Insert/Delete/Rename the sheet etc.
How do 'hide' these options within VBA? Or is there a Menu setting that I can be set to Enabled = False?
message box properties. i m using this
View 3 Replies View RelatedIs it possible to disable The "Look In:" field of the GetOpenFilename dialogue?
What I would like to do is to keep users from selecting folders other than the CurrentDirectory settings and if possible to keep the user from deleting,copying and pasteing to the files in the current dirrectory displayed. The code I have is:
I would like to programatically add information to an Excel file's Properties, the Details tab. I have alot of files in the applicable group. Files are .xls but I'm using Excel 2007.
View 3 Replies View RelatedIs there a way to set the printer's properties using VBA?
Sometimes we set the printer for BEST quality to do photos or brochures. Well if we don't change it back, when we go to print a spreadsheet, it takes forever because it is set on best quality.
So......
I want to be able to set the print properties to normal using VBA.
I have a problem with the PivatTable properties in Excel and VBA. The problem is as follows:
This is a example table:
Sum of store_sales time_id product_id store_id 367 368 369 1 3 6 7 11,4 11 13 14
(the format is not real clear, but I will explain)
This is a part of a PivotTable where:
Sum of Store_sales is located in the datafield(one record; 11,4)
product_id and store_id are Row-Items
Time_id is a column item.
Now, I want the properties of the cell containing 11,4. I've made it so far in VBA that I can ask what his column-items and his row-item are.
Column = Application.Range(chosenCell).PivotCell.ColumnItems.Item(1)
Row = Application.Range(chosenCell).PivotCell.RowItems.Item(1)
Row2 = Application.Range(chosenCell).PivotCell.RowItems.Item(2)
But how do I get VBA to return the valueheaders of those columns and rows? So actually, I want VBA to also return the names: product_id, store_id and time_id. This is because I need those headers to create a query which I send to a Access database.
Is someone able to give me a hint? Is there a method for this in VBA?
where I can find a comprehensive list of '. Cells()' properties that I can Test for/Apply to Excel Cells?
i.e.
Cells(x,y).NumberFormat
I want to set Conditional Formatting using VBA,
to test for:
Data Type (Character, Integer, Date, Decimal, Logical)
Field Length (x(50), 999, 99/99/9999, 999.99, Yes/No)
from an imported file.
I have a workbook with 10 worksheets and I need to know the memory size for each worksheet. I know from File/ Properties that the file is 3.7mb but that is much higher than I would have expected. I can't tell which worksheets are causing it to be so large.
View 5 Replies View RelatedI have attach the lab2.xls files below.
1. Download “ Range Data.xls”.
2. Use the Offset and End properties of Range object to name range from A2 to the end of the column as “NEmployees”, range B1 to the end of the row as “NScores”,and the rest of the range, B2 to F19, as “ScoreData”.
3. Do some formatting using the range names and the With-End With construction: make the font of the NEmployees Range in bold and blue color; change the font of the NScores Range to italic, in red and centralize the text (using the HorizontalAlignment property).
I am trying to use VBA to change the caption of checkboxes in "Sheet 2" when I change the value of a cell "A1" in "Sheet 1".
This code is working:
Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then Exit Sub
Worksheets("Sheet 2").CheckBox1.Caption = "New Caption"End Sub
But there are 6 checkboxes in Sheet 2 and I would like to do something like this:
Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then Exit Sub
For i = 0 to 5Worksheets("Sheet 2").Control("CheckBox" & i+1).Caption = "Box" & i+1Next iEnd Sub
This doesn't work.. So I guess the Control-function is wrong.
I have a button on a worksheet that activates a macro, the macro is stored in a "personal.xlsb" file. This file is copied to several users computers so they can use the macro, problem is once the button is assigned to a macro from one computer all the other users can't use the macro. What can I do to make this macro work on all computers? (Less placing yet another button on the tool bar).
View 2 Replies View RelatedHow does one access the properties of a shape? For instance getting the text on a button ( from the forms toolbar ) on a worksheet. This works
View 4 Replies View RelatedI have a folder with some 126 word document files.
What I need is to create list of file names in a column & its properties like Author, Date modified in adjacent columns of each file..
I have a series of shapes (circles) that I wish to use as a substitute to the radio buttons found in the form controls (too small).
I've drawn the shapes, but wish to manipulate their properties in VBA. For example, when the user clicks on the shape, the macro includes VBA which will change the properties to include colour and fill. Of course, clicking it again, will send it back to the default.
How can I get the VBA of the current shape's (default) properties? Where can I find a list of the VBA properties available to any particular shape so I know what fields are available to set attributes to?
I have 2 sheets to this program. I open the properties to change the ScrollArea of Sheet1 to $A$1:$N$24 so the users that will be using this cannot scroll down to see data. As soon as I hit save, or even Save As and replace the file, then reopen it, I am able to scroll on Sheet1. Now, I do not recall if this same thing was happening prior to restricting access to the workbook or not. One thought I have is because I am the fully authorized user, is that why, even after saving I am able to scroll? Would someone who does not have the permissions that I do in the work book not be able to scroll? Or am I just missing something small?
View 7 Replies View RelatedI am after a control similar to the Properties one in the VBE. The one where you modify properties for a control in design mode.
Does anybody know of a suitable Control that I can use in my project that has similar usage?
I need to have all cells locked and protected, but I need to be able to select all cells in column A only. With 2003 I can chose to be able to select locked cells in the Protection Properties, but that allows at the same time to select all cells and I want a property to allow selection in column A only.
View 9 Replies View RelatedI have had a long-held belief that Excel/VBA methodology is very suited to solving this problem. I have investigated several possible methods noted below and picked one that gives a "quick win". I offer my results so far as a step towards the goal, as well as giving an example of how to manipulate a non-MS Office application using VBA. In this case Windows Explorer - I have used similar code on corporate applications like Oracle and SAP. My 'Write' method uses Sendkeys. Luckily I have been able to do the job without having to use code to simulate mouse functions or using API calls to simulate key presses. Ideally I would like to use something more stable, but there is the bonus that it is simple, as well as changing WMA and both versions of MP3 tag (see below) if present. Perhaps on reading this someone else may have a better method.
Being a ballroom dancer I have a large collection of CDs as well as software to rip to hard drive and enhance the sound quality. Many are quite old. A big problem has been to get a consistent view of the file properties - especially Genre, (eg. Waltz, Foxtrot .. etc.) which, from my own CD burning or external sources, is missing, or incorrect from using the now obsolete ID3v1 tag standard list. Applications such as Windows Media Player and RealPlayer allow functionality to edit tags but become very tiresome when it comes to making bulk changes - such as after burning a new CD. When viewing properties of the same file in various other applications they often show things like Title & Artist switched, and Genre not at all. I have tried software to change MP3 file tags, but find them over-complicated, confusing, and difficult to make the bulk changes I need. With Windows Explorer we can only change 8 properties - but I find these sufficient.
My method is :-
1. READ : Run one macro to put data into a worksheet.....
2. MAKE CHANGES : Make manual changes to the Excel worksheet in the normal way....
3. WRITE : Run another macro to read the worksheet and update the file properties in Explorer.
Part 1 is very simple and robust. Part 3 is difficult because when we use Sendkeys to mimic keyboard entry the code runs too fast to allow time for things to happen on screen, so we have to put Wait statements *depending on how fast the computer runs*. So Slower is better - up to a point.
Properties are added to MP3 files by using a "Tag" - additional bytes of information which form part of the file. WMA files are a Microsoft invention using a similar, but different structure. Interestingly, using my code to make changes via Window Explorer updates BOTH MP3 Tag versions as well as .WMA files. I moan about Microsoft less and less.
MP3 *ID3v1* consisting of 128 bytes always at the end of the file is now 'obsolete' - despite being still in use. This is very easy to read/write using the same code as for Text Files eg.
Open "c:myfile.mp3" For Binary As #1
etc. It is, however, limited to 4 text fields of 30 characters max, 'Year' =4 characters, and 'Genre' is a single character, the Asc() code of which is a lookup to a standard list which contains 125 items - none of which is any good to me. Could have my own lookup I suppose.
MP3 *ID3v2.3* is in the process of being superseded by ID3v2.4. The big problem here is that there are several different versions and the code required is extremely complicated - mainly due to the use of variable length fields. So we not only have to find the property, but read the field length before getting the field contents. Writing would need to change the coded field length. This is further complicated by there being the option to use an "Extended Tag" - *or not* ! Version 2 tag can be at the beginning or end of the file (before ID3v1 if it exists) - or both. The MP3 files on my computer all seem to have both versions - v2 at the beginning and v1 at the end. It is further complicated by the ability to have User Defined fields. I see some of my files have a user defined 'Genre' field, despite having the standard one 'TCON' too. The tag also needs a form of "encryption" so that the mp3 player does not treat it as audio data. Visit here for detailed information http://www.id3.org .
To view an audio file in its raw state open it in a Text Editor. I use 'TexPad' which is very fast and gives a choice of Binary (with Text 'translation' in a column) or Text view. Notepad gives just a Text view with empty space for non-text/binary characters. There is a large number of Null characters Asc(0) in proportion to the overall file length - mainly for "future development" I believe.
I would be interested to hear of any comments, suggestions and code improvements.
READ CODE IS IN THE "REPLY" BELOW