A question from a beginner...

"Unable to record" error message As I try to enter a long formula (while I record the macro) I get a message that says "Unable to record". I'd hate to shorten my formula. Any ideas Tag: A question from a beginner...

Toggle buttons I need some help programming toggle buttons. I assume a toggle button can have 1 of 2 states and the state alternates when the button is clicked. Is this correct I am thinking about some code as follows;
Private Sub ToggleButton1_Click()
ToggleButton4(changeState)
If ToggleButton1.state = false Then
ToggleButton1.caption = "NO"
Else
ToggleButton1.caption = "YES"
End If
End Sub
Can you see what I am trying to achieve When the button is clicked, it swaps from its current state and displaying caption YES or NO to the "other" state and displaying caption NO or YES. Tag: A question from a beginner...

Setting SeriesCollection Intermittently Fails: ERR - unable to set the xvalues property of the series class Hi All, See the code snippet below for reference. I have a worksheet that contains multiple charts (ChartType = xlLineMarker) used to display trend data. The series data for each chart is dynamically reset every time the vba code is run. This reset is based on user input that is defining a date range to run view in the trend graphs. The dates are displayed on x-axis of each chart. For some date ranges the code works fine, while for others I'm get the "unable to set the xvalues property of the series class" error. I've done quite a bit of googling and I have not found a solution. Any help is MUCH appreciated! Additional comments: - sChartName is an array of chart names that get updated each time (never more than 6 charts) - iOffset is predefined variable of the type Integer. It represents the column offset where the series data is to taken from - When the code fails, it fails on chrt.SeriesCollection(1).XValues = rng line - I have validated that the rng object contains the correct range and values for the series (the range is small - never more than a year for a date range) - This part makes no sense to me and may not be pertinent, but I figured I would mention in case it means something to someone else. For single month date ranges (i.e. 1 data point on the chart) the code fails consistently. For date ranges of 2 months (i.e., 2 data points), the code fails intermittently. For date ranges greater than 2 months, the code works fine. I haven't run enough tests to unequivocally state this would happen every time, but in the 20 or so times I've run the report during my troubleshooting, this has been my observation. Again, thanks in advance for any help!
Code Snippet
Set ws = ThisWorkbook.Sheets("TrendGraphs") ws.Activate For i = 0 To UBound(sChartName) ws.ChartObjects(sChartName(i)).Activate Set chrt = ws.ChartObjects(sChartName(i)).Chart chrt.SeriesCollection(1).XValues = rng chrt.SeriesCollection(1).Values = rng.Offset(0, iOffset(i, 0)) If iOffset(i, 1) > 0 Then chrt.SeriesCollection(2).Values = rng.Offset(0, iOffset(i, 1)) If iOffset(i, 2) > 0 Then chrt.SeriesCollection(3).Values = rng.Offset(0, iOffset(i, 2)) If iOffset(i, 3) > 0 Then chrt.SeriesCollection(4).Values = rng.Offset(0, iOffset(i, 3)) If iOffset(i, 4) > 0 Then chrt.SeriesCollection(5).Values = rng.Offset(0, iOffset(i, 4)) Next i Tag: A question from a beginner...

CRM 3.0 Error: Subreport could not be shown I have a user that is getting the error "Error: Subreport could not be shown" when doing a report for a quote. She can do a report for an order but just not for the quote. I tried logging her in with different computer and get the same result so I think it is at the server level. Please give me some direction on what to do next.
Thanks Tag: A question from a beginner...

0x62304390 referenced memory This is definitely a matter of Yahoo Toolbar and/or Browser. Uninstall them and the displayed error will vanish instantly and permanently. Good luck Tag: A question from a beginner...

Can I disable keys in Excel while a cell is selected? I have made use of the onkey function in vba to disable most keyboard shortcuts, however if the user is in the middle of entering text into a cell, he can press any of the disabled shortcuts and they will work. Is there any way to make them stay disabled Tag: A question from a beginner...

Refining MS Access data and saving to csv file using VBA code Hello, I have a table with these sample data pattern: Col1 Col2 001 2 002 1 002 2 002 1 003 4 003 4 003 3 003 3 003 5 The expected output in the csv file will be exactly like this: 001,2 002,4 002, 002, 003,19 003, 003, 003, 003, In the csv file, the number next to the topmost 002 is the sum of the Col2 where Col1 is 002 and the remaining field next to other 002 rows will be blank, the same process repeats for 003 and so on. Would anyone please help me to code this in VBA k Tag: A question from a beginner...

Why my AddIns runs only once!? Hi All,
I made 6 VBA Excel macro using Visual Basic Editor from within Excel, but after deploying the AddIns, my AddIns run only once and stop!!
Here are the details:
I made 6 macros and wanted to share them with my colleagues at work, in what I call it Add In library.
I assigned shortcuts to the 6 Macros as follows
Lnk2DB() (" Ctrl+Shift+a ")
CHS_CHEM2 (" Ctrl+Shift+b ")
CB1_CHEM (" Ctrl+Shift+c ")
CB1_BIO2 (" Ctrl+Shift+d ")
CreateDoubleNoSpace (" Ctrl+Shift+e ")
CreateTripleNoSpace (" Ctrl+Shift+f ")
I assigned the Shortcuts using Tools a Macro a Macros¡­ a Options.
I saved the file as AddIns (xxx.xla), save in shared drive and modified our log in script to copy this AddIns library to c:\Documents and Settings\%USERPROFILE% \Application Data\Microsoft\AddIns ). Which copies the AddIns correctly.
When I tried to run the AddIns from by using the Shortcuts, it runs correctly, but I can not run them again!!! It will run again if I close the Excel Sheet and open it again!
Any idea what I am doing wrong
Thanks for your help.
Aws Tag: A question from a beginner...

Cannot Find Library Have you been able to try Mercury Schroeppel's suggestion of explicitly referencing the VBA library. Have you tested LCase in the immediate window of the troubled pc without your application being loaded Tag: A question from a beginner...

group shapes Hi,
I have a macro that at one place creates new lines (the shape) on a sheet. My question is how can I group these lines. I have tried with something like this but I can not make the range function at the end work. As it is now I recieve an error '1004' The specified parameter has an invalid value.
Any ideas what to do
For i = 1 To myLegends.Count ''' draws the line With ActiveSheet.Shapes.AddLine(BeginX(i), BeginY(i), EndX(i), EndY(i)) .Name = "MyLine" & i End With Next i
Dim shp As Shape Dim sht As Worksheet Dim tmp As String Dim Lines() As String
Set sht = ActiveSheet ReDim Lines(1) i = 1 For Each shp In sht.Shapes tmp = shp.Name If Left(tmp, 6) = "MyLine" Then Lines(i) = shp.Name ReDim Preserve Lines(i + 1) i = i + 1 End If Next
ActiveSheet.Shapes.Range(Array(Lines)).Select
Thankful for help
Jonas Tag: A question from a beginner...

Failure Using the Vxd loader. I have been unable to use either my cd-rom or dvd dirves for the past week. When I load a cd or dvd in the drives, they spin and then nothing. Both drives no longer show up on the Explorer tree. ("Nero Scout" is in their place).
When I looked at 'problem devices' under 'components', in System Info, both drives show the error "failure using the Vxd loader".
I am using Windows Ultimate on a home built system. The trouble started after I uninstalled 'Nero7'. I tried reinstalling Nero but the drives don't respond. Can't even reinstall Vista. I'm thinking the problem is in the registry, but have no idea how to fix it.
Help, if you can. Thank you. Tag: A question from a beginner...

How to determine the cursor position to Excel range There is no simple method to save the image to a file. You can either save the worksheet as a webpage and pick out the generated image from the files created. Or you can use a chartobject, which does have an export feature, to hold the image whilst exporting. I have an addin which does this. The code is open so you can see how it is done. http://www.andypope.info/vba/gex.htm Tag: A question from a beginner...

Automatic query refresh Hello,
I am writing a macro in Excel 2003 that automatically pulls information from a Sharepoint site when the workbook is opened up. I keep getting a Query Refresh box that wants me to either enable or disable refreshing the query. This will be loaded on several different computers, and I can't change the registry on all of them, so I need to know how to enable the refresh without having the user have to do it. Tag: A question from a beginner...

Postpone PivotTable Refresh in Excel 2003 Hi,
I have an Excel 2003 PivotTable that is connected to an Analysis Services 2005 cube. I am using VBA to control which members of the page field dimension are selected (equivalent to using 'select multiple items'). This is working ok, but my problem is that the PivotTable is refreshing everytime I add a member. Is there a way in VBA to stop the refresh and then do a single refresh when all the required members have been added
Any help very much appreciated.
Dave Tag: A question from a beginner...

Application.ScreenUpdating Problems Hi!
I had not met this problem. But maybe you should try Application.EnableEvents = False Then the environment would not change its inner properties at all. Tag: A question from a beginner...

VBA + Bloomberg Hi, My coworker forwarded me this from Bloomberg. He also sent me a file with an example, but I dont think we can post files here. This is the solution to your problem. My coworker also told me that using a pause in your VBA code to wait for bloomberg links in cells to update will pause the updating of the cells as well as the code, so that is a dead end. Hope this helps. From Bloomberg: You could use some of our existing samples and modify them as per your needs. On WAPI<GO> search for "sdkdownldcenter" and download the Bloomberg Desktop API SDk's. Once installed you can find them at C:\blp\API\Ad Hoc Examples\ActiveX Data Control. The SimpleSynchronous sample would be a great place to start. Also if you dont want to have a button, you could replace it with a macro that makes the synchronous request. Tag: A question from a beginner...

PPT: Display total # of pages for handouts I'm trying to print handouts (with 6 slides per page) that will include a footer that displays the total number of handout pages. So, for example, if there are 14 slides, the handouts would show that there are 3 pages. Using this code works for displaying the total number of pages for the entire presentation:
Code Snippet
With ActivePresentation.Slides AddOne = IIf((.Count Mod 6) = 0, 0, IIf((.Count Mod 6) < 5, 1, 0)) TotalPages = (.Count \ 6) + AddOne End With However, I was wondering if anyone knows a way to handle print ranges. So if I select a group of something like 8 slides to print, is there a way that I can get it to display 2 as the total number of pages Any help would be greatly appreciated. Tag: A question from a beginner...

IsError function from VBA6.3 to v6.0 It did try it in xl2000 and xl97 without error. If you want to email a test file that fails I will take a look. Check my profile and use the email address on my website. Tag: A question from a beginner...

Vba Excel 03 - Code to detect before Sorting / deleting Rows Hi Aldo
I've been thinking about it again. What I've given you is more of a general solution. However, there is a simpler way if there are a few restriction e.g. the same row heights across all the relevant rows. Try this ...
Code Snippet
Sub Test2()
Dim strCaller As String Dim lngTop As Long Dim lngRow As Long
' who called us
strCaller = Application.Caller
' what is the top of the shape
lngTop = ActiveSheet.Shapes(strCaller).Top
' divide the top of the shape by the row height to get the row number
lngRow = (lngTop \ Rows(1).Height) + 1
' now we have the row
MsgBox lngRow
End Sub
Regards
Peter Mo. Tag: A question from a beginner...

creation of a character constant I wrote a little article on this matter:
http://metasharp.net/index.php title=VBA_creation_of_a_character_constant
here's a copy:
Creating a character constant might be a pain. Hopefully here at MetaSharp, we found a way.
Here's how we declare it very simply: Code Snippet Public Const MYCONST As String = "a"
However I can hear you: easy. Now let's go next step. How do I do the same for non printable characters Here's what I tried at first: Code Snippet Public Const MYCONST As String = Chr(29) ' non printable character
Of course it doesn't work because a constant can't be initialized with a function (Chr here) return value.
I also tried to do the following... Code Snippet Public Const MYCONST As String = vbNullChar + 29 ' non printable character (that is: Chr(0) + 29)
...didn't work.
And also this... Code Snippet Public Const MYCONST As String = " " ' non printable character ( is obtained by ALT+29)
... didn't work either. (ALT+ method uses the PC850 keymap, not the ascii table)
Here we are, the final solution: Code Snippet Public Const MYCONST As String = " " ' non printable character
This one is correct and pretty simple. The big question that should hit your mind is: how can I get to type in my code the character corresponding exactly to any ascii value, even non printable ones
Here's how. Create this method in an Excel module:
Code Snippet Public Sub Test() Dim C as String C = Chr(29) C = "done" ' put a breakpoint with F9 on this line End Sub
set your breakpoint and run the method (with F5 for example).
add a quickwatch on the C variable
double clik on the value field of C variable
copy paste the displayed character from the debugger window to your code
Success Tag: A question from a beginner...

dll's in VBA Currently in my VBA programs, I'm finding myself repeating a lot of code and functionality across different projects, and it would be great from a maintenance point of view if I could create something similar to a .dll, that I could import into a project and use. Furthermore, if circumstances change and I need to change how a sub-routine works in the .dll for example, all projects using it will benefit from the updated code.
I know that you can export modules and class modules and then import them into other projects, but this seems a bit limited. First, if i wanted to change how a sub-routine worked in either the saved .bas or .cls files for example, how do I do that Second, how would projects that use either of these two files benefit from the updates, without having to re-import them
This is probably a very common topic that has been posted before, but I need a quick reply if possible.
Many thanks for any help!! Tag: A question from a beginner...

How to delete a connection? Dea MSDN, This was very helfull. Is there and way of deleting these connections by name Is there a reason why the name is given to the connection from the querytable yet not usable in a workbook.connections property Thanks Johan Tag: A question from a beginner...

Action Pack Question Don't even think about NOT renewing!
Like Tony Soprano said, "Once you're in this family, there's no gettin' out".
Be prepared for all kinds of nasty, threatening letters from the Microsoft legal team. Think they¡¯re not tough Heck, they regularly beat the US, as well as other governments with ease.
According to the agreement, if you do not renew, you¡¯re obligated to un-install any of the licenses used by the action pack. Sounds fair enough right Only catch is, they will threaten to show up at your door, unannounced; home, office, wherever, and audit any and all computers for ¡°unlicensed software use.¡± I heard its worse than an IRS audit.
By the same token, anyone can go ahead and buy oem software, legally, all over the Internet. Oh, it will have to come with a ¡°qualifying piece of hardware¡±, so they¡¯ll send you a CD ROM audio cable.
It¡¯s a shame, those that play by the rules, like us who pay year in and year out, are always the ones most exploited. Tag: A question from a beginner...

WoksheetFunction Doesn't Work With Currency Range Hi All,
I got a probelm with Excel VBA. I have a range named as "MyRange" with currency data. I would like to add up all currency numbers in the range. I used the following codes:
Dim MyRange
Dim MyCurrency
MyRange=Range("A1:A3")
MyCurrency=Application.WorksheetFunction.Sum(MyRange)
MyCurrency returns only 0 whatever currency values in MyRange.
Please help and thanks in advance
Qingping Tag: A question from a beginner...

VB to MS Acess (ASNI To UTF8) what can i do i develop URDU language software in VB 6.0 but when i stor record from VB to Acess Database it is not in UTF8 can any one help me
how can i stor records in Acess MDB via VB 6.0 in ANSI to UTF8 fromat
plz help Tag: A question from a beginner...

Alphabetize upon concatenate In Excel 2003, I have two columns of information (intersection street names) Oak and Main for example. I would like to combine the information into one cell seperated by an ampersand (Main & Oak). I need to alphabetize the street names upon concatenation so that Main always lists before Oak. Suggestions Any advice is appreciated.
BPC Tag: A question from a beginner...

meisaka

Hi all:

I am new to the programming world, and I am interested in learning VBA. So, please provide some tips where should I start

Re: A question from a beginner...

Keithyboy1

VBA Programming for Dummies by John Walkenbach is an excellent book to start off with. It helped me grasp the basic concepts of Object Oriented Programming and working with objects, which to a beginner can be very confusing! See link below: