After 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 ....
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?
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?
I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
.
. extending until the end of the data set
.
I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:
Code:
Public Sub CSVparser(file As String)
Dim Lines As Variant
Dim j As Integer
Lines = Split(file, vbLf)
ReDim breadCrumbs(UBound(Lines)) As Variant
For i = 1 to UBound(Lines) - 1
breadCrumbs(i) = Split(Lines(i), ",")
Next i
End Sub
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
In the properties in one of my wb's the Size measures to 1,40 MB, but the Size on disk only measure 408 KB. If I make a copy of the same wb, the copy displays 1, 40 on both.
Why do I have a different size on the original?
When in a Combobox from the Control toolbox toolbar, the data for the Listfillrange are in a column (e.g.: myrange = $A:$A), it works fine. But when the data in myrange are in a row (eg.: myrange is =$1:$1) , the combobox shows nothing!. What can I do to show the combobox all data (A1,B1,C1,D1 - last data?) ?
View 9 Replies View RelatedIs it possible to create a userform that mimics the Properties userform using VBA by itself. I'm not sure what the form style would be called otherwise I'd have googled it; but it looks pretty cool.
I have a tree view control property in a userform working fine, however, when the form open, only the top level is list (ie, only 1 string is visible). In order to see more, then user needs to click on the + which is not an issue, but they would rather have the tree open up to certain level, say 5 hierarchical levels down, instead of just the one string.
View 4 Replies View Related