I have a userform with fields (TextBoxes) Num1, Num2, Num3, Num5 and Num5
I also have an array which i have looped through and seeded with five values.
Now when I try and loop again and pass the values from the array to each of the textboxes I cant remember the syntax to reference the textbox using the loop variable - Num(var). I've experimented with the & symbol but it escapes me at the moment !
I have a fairly big excel spreadsheet with about 2 userforms at the moment. One Userform (the main one) has a multipage control with about 12 pages. Now here is the weird part. I open up the excel document and this main user form loads up simply with this code
Sub Auto_Open() Load Userform1 Userform1.show End Sub
This works fine the form loads. Now on this form there is a multipage control with about 12 pages as mentioned above. Now everything works the way I want it to eg the text boxes, combo boxes, command buttons etc all work and link with the worksheets fine just the way they should...But if I reference or try to use a couple of the controls in any way excel crashes. It doesn't give any errors or anything it just crashes and then I get a prompt saying do you want to send the error report blah blah. I have narrowed it down to 3 command buttons and 1 combo box so far that this happens on. If I do the most basic of commands on any of these controls such as something as simple as msgbox problemcombobox.visible...
I have a sub that I need to call multiple times. I'm trying to figure out if there is a better way to do this rather than write out the call each time:
AYForm = "UserForm2.txtAYMonths" & i AYPForm = "UserForm2.txtAYPercent" & i SummerForm = "UserForm2.txtSummerMonths" & i SummerPForm = "UserForm2.txtSummerPercent" & i JobForm = "UserForm2.cboJobClass" & i NameForm = "UserForm2.txtName" & i
For i = 1 To 15 Call FormatAY((AYForm), (AYPForm), (SummerForm), (SummerPForm), (JobForm), (NameForm))
' Call FormatAY(UserForm2.txtAYMonths & i, UserForm2.txtAYPercent & i, UserForm2.txtSummerMonths & i, _ ' UserForm2.txtSummerPercent & i, UserForm2.cboJobClass & i, UserForm2.txtName & i) Next i
the sub FormatAY has six arguments; 5 textboxes and 1 combobox. Since the names of these items are the same except for a number (example UserForm2.cboJobClass1, UserForm2.cboJobClass2, UserForm2.cboJobClass3 etc...), I wanted to write a for loop and use a variable to represent the last number. Is this even possible? The couple of ways that I have tried this, I get either a member not found, or ByRef error.
In VBA (Excel 2003), I'm wanting to get a reference to an ActiveX comboBox that is inside/attached to a cell.
I can iterate thru all the OLEObjects in the OLEObjects collection of the Worksheet and test the .TopLeftCell property to see if it matches my target cell. However, this seems like a long way around the problem - particularly if there's hundreds of comboboxes in the sheet.
Intuitively, I would have thought there would be a Cell.OLEObjects collection, but it appears that there isn't.
Is there a quicker and easier way to get a reference to the OLEObjects within a target cell?
I recived the small job of making a template for my company for travel. I had spare time so I added in a feature so they can just e-mail it to the approite person from clicking a command button intead of taking the time to click the built in excel e-mail function and typing the addresse.
Anyway, this from tells the person who it is being e-mail to, what flights to book.
I have differnt command buttons to bring up differnt airline webpages, bascially a hyperlink but in command button form. Is there a way, just because i have time, to take the flying dates from excel, input them into the webpage which the command brings up, and search for the flights for that date, just by clicking a button in excel?
I was reading Box Office Mojo and wanted to know which actor would have the highest box office total if worldwide sales were accounted for and inflation was considered (for some reason they offer the data in 3 different formats but that is not one of them). I had previously only used Excel for simple records so I had to search your site for ways to write a few macros but I got everything working. I want to present the data.
It looks like embedding a spreadsheet in a webpage can be done but is there anyway I can add a feature so that if the person browsing the site clicks on a cell that cell's data will removed from consideration? I don't want the cell to actually display a "0" but if the background color could change to signify it was no longer being counted that would awesome. A lot of people argue about what movie should be counted toward an actors total box office record (starring roles only, supporting roles, voice work, cameos) so I would really like to be able to allow people to easily remove roles they personally don't think should count towards the total.
I am currently working on a VBA Macro in order to scrape data from a webpage into an Excel spreadsheet. My problem originates from the type of webpage that I am accessing. I am accessing a webpage that is used to filter the type of information, in which I have a Favorite (preset), which is the URL that I have included in the code. Once I get to that page, the code selects the proper function to load the data onto another webpage. It is from this webpage that the data I need to import into the spreadsheet is located.
Here is a step by step breakdown of my macro and the bolded/underlined portion .
Macro initiated - > filter webpage opened - > selection of filter preloaded - > data loaded into another webpage - > webpage with data opened - > | need to transfer data to Excel |
I have a simple text box I need to fill in with my email which I have managed to do very simply with this code;
Dim IE As InternetExplorer Dim sIEWindowName As String Set IE = New InternetExplorer IE.Visible = True
The layout looks like this with my email.value entered in.
I'm using Access 2007, but that is irrelevant for the VBA.
My problem is I can't enter the filename as easily. I have to use SENDKEYS to TAB over then a bunch of other tacky SENDKEY commands to load my file. Even though it works I don't feel it's reliable and would rather just fill the file name in a bit easier.
I used IE developer to show before and after using the BROWSE button to load the file location as seen in pic 1 and pic 2.
The code below has been my best attempt. It runs without error but it does not give me the result I need which is a file location for the application to pull.
have been trying to work out how to copy data from a website into a worksheet. For some reason the Data > Import External Data > New Web Query route does not work so I have tried the code below but cannot get this to work either.
What I am trying to achieve is to copy each of the various tables on the page into Excel.
Dim WebCopy As Object Dim WebtitleCopy As Object Set WebCopy = Sheets("Sheet2") WebCopy.Cells.Clear
I'm trying to enter some in a website and submit it, I'm using this code:
Code: Sub LoginToSite2() Dim IE As Object Dim IEdoc As Object Dim Password As Object
However I can't get to work for this website, I'm getting Run-time error "91" Object variable or With block variable not set I can only post the source code of the website (below). Also it contains frames, maybe that's part of the problem.
I've been working with this script to import and paste a webpage to a worksheet. Once the webpage is on a worksheet I can pull it into VBA easily enough but I was wondering if there's a way to "paste" the webpage directly into an array in VBA and skip the paste to worksheet step?
Set IE = CreateObject("InternetExplorer.Application") With IE .Navigate theURL IE.Visible = False End With
I am using VBA to pull a web query to get sone info from an intranet site.
Unfortunately, the data it pulls is usually old. I've found the only solution is to open a browser manually and to click the Reload button on the web page then navigate back to my workbook and run it again. Then it will pull the most up to date info.
I've tried several ways of forcing a refresh of the data, but have not figured it out yet.
I have been trying the past couple of hours to use the send keys button to tab down to the button on the page I need and to hit enter, thus forcing a refresh, but I can't seem to get that to work either.
If I copy the source to the notepad, I can paste it to the spreadsheet IF I use the "PASTE" and then click on the "Use Text Import Wizard". This works OK, but, I am needing to do this from INSIDE another macro, and I can not rely on the users, so I need this to be automated. I tried to RECORD the steps, but no matter what, it records only:
Sub Macro1 Range("C1").Select ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _ DisplayAsIcon:=False End Sub
But does NOT include the selections I made with the wizard (delimited with "`") or FIXEDWIDTH. Eitherway works fine, but the macro won't. Attached is a sample of the source. Saving the Notepad, and then processing the FILE is not an option. I need to work straight from the clipboard.
I have spent hours looking for the different options available for the "PasteSpecial" but all I find is more people asking what the options are.
File import, I found out how to control the data, same with convert to text. But it does not seem to be "quite" the same
I'm trying to use the CreateObject("MSXML2.XMLHTTP") technique to log in to my Amazon Seller Central account and extract the HTML page source of the URL. However, I simply haven't been successful with getting it to log in. I should note that I know very little about this technique, and simply copied and slightly modified the code from a website. It works when there is no log in. Here's an example of the
This is the code I use to get the page source when I don't need to log in. However, the URL I'm now trying to get to requires me to log in first. I've come across a couple different sites that offer the following techniques:
I am having trouble pulling data from a website. I need to get data from this website:
I need the highs and the low temperatures for the next week. (just the numbers preferably). But when I go to Data>> Import External Data >> New Web Query i'm not able to get any of the numbers into Excel. This way worked fine with other websites.
I am trying to write a macro that will allow me to add task to [url].... (task managing webpage) from data within excel. I could easily write a macro that emails the information to my toodledo email address, but this is not what I would like to do.ow to start the code utilizing Toodledo's API, which can be found here: