Free Microsoft Excel 2013 Quick Reference

register any "mscal.ocx" using vba

instead of, Windows Start menu, choose Run, RegSvr32 ~~~~~

is it possible automation register using vba(excel macro)?

*** Sent via Developersdex http://www.developersdex.com ***


I need to register DLL's in the sytem using VBA?

How can i do that?

I'm using MSCal.OCX to have a user select a date from a calendar in a user form. It all works fine on my machine and the machines of my colleagues. All of these are running XP Pro with Office Pro, Excel version 11.8120.8122. All have SP2 installed.

The problem comes when the customer tries to use it on their machine. They are running XP Pro with Office for Small Businesses, version 11.6560.6568.

I have done a search through Google and I have made sure he has the most up to date version of MSCal (11.0.6650.0) and registered it, using regsvr32 mscal.ocx. I've even made sure he has unregistered and deleted any previous versions. The error message he is getting reads: "Compile Error in Hidden Module: Module 4". This comes up when the calendar is supposed to appear. I have also tried getting the user to register the control in Control Toolbar -> More Controls -> Register Custom Controls but they still get the same error.

Can anyone help?

Sorry if this is in the wrong forum.

TIA

Hi everybody,

I know this is a common issue that many of you are sick of, but I am really struggling and hoping for some clarity. I couldn't find exactly what I was looking for with the "search" tool on this forum.

I have a client that does not have calendar control. You will have to bare with me with my technical lingo (I am not a computer guru, I just know how to create VB programs in Excel):

Client is using XP and has Office 2007 installed on his Dell PC.

When clicking a button that should have a calendar appear on a form, they get the following error : “Could not load an object because it is not available on this machine” and when entering through it, is sent to VB and receives the compile error “could not find project.”

When in VB, I noticed in Tools --> References that Microsoft Calendar Control 2007 was not an option to check off. He also mentioned that Microsoft Forms 2.0 Object Library was not checked off (but I want him to clarify that because I don't think that is true).

Similarly, from the Control Toolbox, Calendar Control 12.0 was not an option under "Additional Controls."

I talked him through it and found out that he does have the FM20.dll file in the C:WINDOWSsystem32 folder. This is Good.

He did NOT have the MSCAL.OCX file on his machine (specifically the C:Program FilesMicrosoft OfficeOffice12 sub folder), nor any of the other MSCAL files (such as the .CNT, .DEP, and .HLP).

I have been Googling the error and the MSCAL.OCX and I keep reading things like "registering" and what not. Let's just say I got confused. My client did download MSCAL.OCX from some website (www.ocx.dump/download-ocx-files_new.php) and had him save it to his C:Program FilesMicrosoft OfficeOffice12 sub folder, but this did not help. I told him to reboot the machine and let me know if it helped, but he hasn't gotten back to me, so I am assuming not.

Basically I am looking for some advice.
1) Is the MSCAL.OCX file all that he is missing?
2) Does he have to "register" this MSCAL.OCX and if so, what does it mean to "register" it?
3) Is there an executable .exe file for him to download that will have everything (the .CNT, .DEP, .HLP files - are these necessary as well)?

Basically, and remember I am by far not a computer guy here when it comes to needing objects and libraries and whatnot, what do I need my client to do so that he can get a calendar to appear?

Any information would be greatly appreciative. Thanks,

Hi folks.

I download Visa account as a CSV (XL) file then import the file into a master sheet manually & I then run a macro to format the sheet.

I use this code

Code:
Range("A1").FormulaR1C1 = _
        "=""Visa ""&TEXT(R[2]C,""dd/mm"")&"" - ""&TEXT(EOMONTH(R[12]C,0),""dd/mm"")"
to get the heading I want something like this Visa 01/06 - 30/06.

Again this code has worked fine up until now & I know the problem rests with using the double quotes. I thought of breaking that line of code up & using helper cells & then concatenate the cells.

Any suggestions?

Cheers

Ok, I'm trying to open and update a MS Word document from Excel using
VBA, but everytime I try to open it, it loads as a Read Only Document,
giving me the message that this file is locked for use by myself, and
thus not allowing me to save it. I'm using this code to open it (the
SendKeys does not appear to make any difference to this message):

Sub Button13_Click()
Dim wdApp As Object
Dim wdDoc As Object
Dim sFname As String

sFname = "R:SALESQuote GeneratorsQuotes2 Pass Tray2 Pass Tray
Quote.doc" 'File Location

If sFname = "R:SALESQuote GeneratorsQuotes2 Pass Tray2 Pass
Tray Quote.doc" Then

Set wdApp = CreateObject("Word.Application")
wdApp.Documents.Open Filename:="R:SALESQuote Generators
Quotes2 Pass Tray2 Pass Tray Quote.doc" 'Opens File
SendKeys "{Left} {Enter}, False" 'Updates File
End If
End Sub

Can anyone help me to be able to open it as a non-read-only file...?

Hello everyone.

Some people throughout our company were having problems using the excel form I created which contains a popup calender. Even with the mscal.ocx installed some people were having issues. I saw a lot of posts regarding this issue, and thought that this might help.

I found a popup calender that can be integrated into the form, and doesn't rely on the mscal.ocx. I put this into the form (that MANY people here helped create), and now all of the people that use the form no longer have any issues.

Full credit goes to the author Phil Johnson. He has granted me permission to post this here so that others may benefit from it as well.

Thanks to all that help with my project.
Mark

I am having a problem doing this. When I use the following statement:

I get an error. The obvious reason, to me anyways, is that ths is caused by the usage of quotes in the formula and the way
VBA handles said quotes. Is there a way to assign that cell the formula using vba and have said formula contain the quotes
required? 

Thanks again!

I am using the calendar control (MSCAL.OCX) on a userform in Excel 2003 and it works fine but I understand that MSCAL.OCX is not included with office 2010. I'd like to use an alternative to MSCAL.OCX to avoid a problem when my users move to office 2010. Are there any other calendar functions that are common to both office 2003 and office 2010?

I'd like to use the Calendar ActiveX component, but it's giving me headaches because MS decided not to ship/install/activate it consistently. I have a Windows 7 system that gets an error because it is not installed... all my other test system seem to have it configured and working correctly (XP & Vista).

So I have some options....

Option 1
If I could detect the missing Cal functionality at the start of my program, I could avoid the "Could not load an Object because it is not available on this machine." error to the user -- then tell then to go off and install it. Yea - like that's real friendly

Option 2
I could rework my installer to install a version of the MSCAL.ocx but which version? If I install a newer version on an older OS will that give me issues? or break existing functionality?

Option 3
Don't use the calendar object at all, just create 3 drop-down values, one for the month, day & year. Allow the user to pick from those and forget the pretty calendar.

Option 4
Find something or someone that has created a Calendar that does not require the use of the possibly missing MSCAL.ocx.

I'm leaning towards option 3.

Thanks,

Hi,

I have a situation where i have headers in a file but just because there is a header does not mean there is any corresponding data in that col. I was keying off if just the header was present then i need to do "something" but now i need to key off two things:

1) Header present
2) If there is anydata in the col when a header is present.

Question: How to use vba to determine (quickly) if there is any data in a given column.

I created an XL add-in using the mscal.ocx controller, a drop-down box and a
couple of command buttons. Basically, the user can use the calendar control
on the form to select a date, then use the drop down menu to add a time
stamp, hit OK, and the date/time is inserted into the cell.

When I sent the .xla to someone for testing, the dates in the calendar were
so small they showed up as dots. When he looked at the calendar controls
properties, the dayfont was set to '2' and he was unable to change it. I
went back and added a DayFont.Size = 8 during the form initialize event, but
this didn't work either.

The one difference that I later found out was that I am using XL2002 on XP
SP2, while he is using XL2003 on XP SP2. Any ideas on what else I can look
at or solutions to this problem? Thanks for any help you can give me.

Can I get the Formula(s) in any cell as Text using VBA written to another cell(s).!

Dear Forum,

Is it possible to get Formulas in any cell or a Given Range in a Single Row or Row below Column Headings to be written as text to another cell..

Example:

Lets Say Col A - A1 - Sr.No - COlumn heading.
CEll A2 = ROW()-1 is the formula in the cell I need to have this formula written in another cell..

I know I can do it manually but I want this to be done using VBA as there are lot of formulas which are already there and typing them would take a lot of time..

Lets say I need this formula to be written as Text like "=ROW()-1" in cell Z2..

Is this possible, like ti Just give the range and then i get these formulas as TEXT VALUES in different cells..

Warm Regards
e4excel

Hello All,

I am new to VBA. I have a workbook where new rows are added to a few different worksheets every day (about 10 to 30 rows per worksheet).

These rows need to be copied to another workbook. I was successful in using VBA to copy the rows, open the separate workbook, and activate the workbook window. I get an error though when I try to activate sheet2 (subscript is out of range). The same happens when i try to activate other worksheets.

Any help or advice would be appreciated. Thanks.

The code errors out on the following:


	VB:
	
TradeManager.Sheets("Sheet2").Activate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

and


	VB:
	
TradeManager.Sheets("Sheet2").Range("A2").Activate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Below is what I have so far.


	VB:
	
 TransferLALtoInterpolator() 
     
    Dim Interpolator     As String 
    Dim TradeTickets    As String 
    Dim TradeManager    As Workbook 
    Dim NewTrades       As Workbook 
    Dim TC              As Long 
     
    Interpolator = "KRWCommodityInterpolator_Development_23APR12" 
     
    Application.ScreenUpdating = False 
     
    Workbooks.Open Filename:="J:GC Commodity Trade Spreadsheet April2012KRWCommodityInterpolator_Development_23APR12.xlsm" 
     
    Set NewTrades = Workbooks("New Trade Spreadsheet_Development_24APR12") 
    Set TradeManager = Workbooks("KRWCommodityInterpolator_Development_23APR12") 
     
    TradeManager.Sheets("Sheet2").Activate 
    Rows("2:1000").ClearContents 
     
    NewTrades.Activate 
    With Sheet1 
        .Cells.Copy 
        .Cells.PasteSpecial xlPasteValues 
    End With 
     
    Application.CutCopyMode = False 
     
    Sheet2.Activate 
     
    TC = Range("B10").Value - 1 
     
    Sheet1.Activate 
     
    x = Range("B65536").End(xlUp).Row 
    y = Range("B65536").End(xlUp).Row - TC 
     
    Range("B" & y & ":B" & x).EntireRow.Copy 
     
    Windows(Interpolator).Activate 
    TradeManager.Sheets("Sheet2").Range("A2").Activate 
    Selection.PasteSpecial xlPasteValues 
    Application.CutCopyMode = False 
    ActiveWorkbook.Save 
     
    Application.ScreenUpdating = False 
     
    MsgBox "Next Step: click PREPARE OTC LAL ENERGY FOR TRADE MANAGER", vbOKOnly, "Transfer Complete" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello again,

What I am currently working on, is forming a graph of a specific raw data sheet. There are 2 area's of data that go into this graph. One is temperature (x-axis) and one is Tan Delta (y-axis). Once this graph is formed what I want to do, is find the maximum tan delta value from the raw data sheet. Then find the temperature value that is associated in the column to the left of it. I want to take that tan delta value and put it into a textbox on the graph.

I can't figure out how to insert a textbox with the value from the raw data sheet onto the graph using VBA. Any help with this would be awesome

Thanks a lot!

The example code I have forming this graph is shown below.


	VB:
	
 tgchart() 
    Dim x As Integer 
    x = 0 
    Dim y As Integer 
    y = 0 
    Dim LastColumn As Integer 
    LastColumn = 0 
    Dim p As Integer 
    p = 1 
    Dim tgchart As Chart 
    Dim other As Range 
    Dim frequency As Range 
    Dim title As String 
     ' Set various application properties.
    Application.DisplayAlerts = False 
    Application.ScreenUpdating = False 
    Application.EnableEvents = False 
     
    ActiveSheet.Shapes.AddChart.Select 
    ActiveChart.Parent.Name = "TgGraph" 
    ActiveChart.SetSourceData Source:=Range("'Raw Data'!$A$1:$A$1") 
    Set tgchart = ActiveChart 
    With tgchart 
        ActiveChart.ChartType = xlXYScatterSmooth 'Type of graph
         
         ' Remove any series created with the chart
        Do Until .SeriesCollection.Count = 0 
            .SeriesCollection(1).Delete 
        Loop 
         
        ActiveChart.ApplyLayout (1) 
        ActiveChart.ChartTitle.Select 
        ActiveChart.ChartTitle.Text = "Eplexor - Tg" 'Chart Title
        ActiveChart.Axes(xlValue).AxisTitle.Select 
        ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Temperature (°C)" 'X-axis
        ActiveChart.Axes(xlValue).AxisTitle.Select 
        ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Tan Delta" 'Y-axis
        ActiveChart.Legend.Select 
        Selection.Position = xlBottom ' Moves legend to bottom of chart
         
         ' Loops through raw data
        For y = 1 To 1000 
            Cells(1, y).Select 
            If Cells(1, y).Value = "" Then 
                Exit For 
            End If 
            title = Cells(1, y).Value 'Creates Title of graph
             
            Cells(4, y).Select 
            Range(Selection, Selection.End(xlDown)).Select 
            Set frequency = Selection 'Selects x-axis data for chart
            Cells(4, y + 1).Select 
            Range(Selection, Selection.End(xlDown)).Select 
            Set other = Selection 'Selects y-axis data for chart
             
             ' Plugs all data selected in previous section into chart
            Set srs = .SeriesCollection.NewSeries 
            With srs 
                .Name = title 
                .Values = other 
                .XValues = frequency 
            End With 
             
            y = y + 1 '# of cells until next (0C) graph
        Next y 
    End With 
    ActiveSheet.ChartObjects("TgGraph").Activate 
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Tg Chart" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Sample Data would look something like this, but with more numbers:

Temperature Tan Delta

21.42 1.1234
-321.21 1.3453
-7.77 1.0432
9.543 2.9322

Hi,
Is it possible to create a pivot table using VBA where the source is an ODBC source? In my case it's a tab delimited file.
Attached is my code. Instead of reading the file, an ODBC data source selection dialog box appears instead when ActiveSheet.PivotTableWizard is executed (lv_src has a valid value):


	VB:
	
connectString = "ODBC; Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=" & lv_src & "; Extensions=asc,csv,tab,txt;
UID=;;" 
QArray = Array(ConnectString, "SELECT * FROM InvSales.txt") 
ActiveSheet.PivotTableWizard SourceType:=xlExternal, SourceData:= _ 
QArray, TableDestination:=Worksheets(lv_pivot).Cells(1, 1), TableName:="PivotTable1" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help very much appreciated!

I am trying to use a macro to enter in the following formula into a cell:

=IF(A5="N/A",,A5)+IF("A6"=N/A,,A6)

I know how to place a formula in a cell using vba, but this formula doesn't work because there are quotation marks indicating text. Here's my attempt at vba code:


	VB:
	
Worksheets("sheet1").Cells(1, 1) = "=IF(A5="N/A,,A5)+IF(A6="N/A",,A6)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Anyone have any ideas?

Hello All:

I need help creating a VBA code that finds the last column within the print area viewed in Page Break View. The tricky part is that one of the cells in the last used column has text that overflows into 10 adjacent cells. This can be seen in the picture below (and also in the attached Excel file). The critical overflowing cell is highlighted in yellow.

book3.jpg

For my purposes, I cannot use the built-in column autofit feature. Also, for this worksheet, I have the page setup to print to 1 page wide by 1 page tall in landscape orientation.

Using VBA code, how can I determine that Column S needs to be the right boundary of my print area?

Any help is greatly appreciated!

Book3.xls

Here is what i have,

I want to run solver through VBA, i want to change the constraints using VBA, then have that go into the solver program, then export certain results to a different sheet.

I got the solver down and the exporting down. But i can not figure out how to do the following.

for simplicity lets go with A1 to A3
I want A1 to go from 1 to 49, A2 from 1 to 29, A3 from 19 to 28

What i want is to have every combination possible between these three, like,

1,1,19
1,2,19
ect..

So basically something like,

Range("AQ10").value = A1

This will alow me to run solver through a set of data points and then create a table of data. Thats what i want. Any help would be appreciated.

I'm trying to figure out how the return the contents of another cell. For example, I want cell B2 to reflect the contents of cell A1 such that if the spreadsheet user changes the contents of cell A1, it show up in B2 as well, and I need to set this up using VBA.

In excel, if I type "=A1" into cell B2, then whenever the contents of cell A1 is changed by the spreadsheet user, the contents of cell B2 automatically changes as well without running my VBA macro again.

Using VBA, if I do: cell(B2) = cell(A1)
cell B2 contains the contents of cell A1, but if the user changes the contents of cell A1, cell B2 does not update to reflect that automatically.

Thanks for any help you can offer,
Steve

Hi

Is there a way to highlight a cell if the figure is negative?

=IF(A+B

I am trying to use VBA to create an entire userForm from code. For a previous spreadsheet I had already created the userform that I wanted, but now I'm trying to make the spreadsheet more applicable to scenarios outside of my original.

The following code creates the userform, adds the multipage with # of tabs and names created by ranges in the spreadsheet. But I can't figure out how to get the code to add textboxes (checkboxes, comboboxes, etc.) to each page of the multipage.


	VB:
	
 MakeForm() 
     
    Dim TempForm As Object ' VBComponent
    Dim FormName As String 
    Dim NewButton As MSForms.CommandButton, NewMulti As MSForms.MultiPage 
    Dim NewPage As MSForms.Pages 
    Dim NewTbx As MSForms.TextBox, NewCbo As MSForms.ComboBox 
    Dim NewChk As MSForms.CheckBox, NewLbl As MSForms.Label 
    Dim TextLocation As Integer 
     
    Dim loopvar1 As Integer, loopvar2 As Integer, loopvar3 As Integer 
     
    Application.VBE.MainWindow.Visible = False 
    Application.ScreenUpdating = False 
     
     '   Create the UserForm
    Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3) 
     
    With TempForm 
        .Properties("Caption") = TempForm.Name 
        .Properties("Width") = 350 
        .Properties("Height") = 350 
    End With 
    FormName = TempForm.Name 
     
     '   Add a MultiPage
    Set NewMulti = TempForm.designer.Controls.Add("forms.MultiPage.1") 
    With NewMulti 
        .Pages.Remove (1) 'must delete the two pages that are automatically created
        .Pages.Remove (0) 
        .Name = "MultiPage1" 
        .Width = 340 
        .Height = 250 
        .Left = 0 
        .Top = 5 
        .Pages.Add ("Page 1") 
        For loopvar1 = 1 To Range("numTabs").Value 
            .Pages.Add (Range("tab" & loopvar1 & "name").Value) 
        Next 
    End With 
     
     ' loop to add features to the plan design tabs of multipage
    For loopvar1 = 1 To Range("numTabs").Value 
        Set NewPage = TempForm.designer.Controls.forms.MultiPage.Pages(loopvar1) 
        For loopvar2 = 1 To 4 'TextBoxes
            With NewPage 
                 
                 'this is where I want to add the items to go on the multipage
                 
            End With 
        Next 
    Next 
     
    VBA.UserForms.Add(FormName).Show 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Tabs other than "Page 1" will be the same in design.

I'm expecting that the issue is in the Set NewPage command, but then I also can't find the right command to create the items wanted.

Here's what the existing form created in VBE looks like and what I'm trying to replicate through VBA.
userform.JPG

Thanks for any and all help.

Hi,

I am fairly new to VBA and need help with a report to create multiple charts. In the report you will see two tabs one marked Data the other charts. In the charts tab I have manually created two charts using some of the data from the Data tab. You can see it is a fairly simply pattern. Columns C and D are the titles for the series, and I need charts for every 4 rows. After every 4th row I would like to create a new chart. Row E6:BE6 (date) serves as the X value, while the data below are the values for the chart. I have only created two charts but you can see that I need to create charts for all the values. The problem is that the number of entries, will change. Here I have 3 set of 4's. I could have X number of sets of 4's. I need to write code to make sure I catch all the data. I have tried many different types of loops but can't seem to get it. It would be great if the charts could form right under each other, but that is not that important, as long as they don't form right on top of each other. Also exactly how the charts look is not that important either, something like what I have would be great. Chart title and series names are important...i.e. they need to keep changing as the data changes. If anyone needs more info or has questions please just let me know. Please any help anyone can give is greatly appreciated.

Tom

Sorry I asked this question on another board.... here is the URL so no one has to duplicate work. You can check there before answering as well. I really do appreciate any and all help. I just was looking for help from as many places as I could get.

http://www.excelforum.com/excel-charting/758152-creating-multiple-charts-using-vba.html

Hi,

I was hoping for some help with an excel function.
Unfortunately my company prevents me from using VBA or Macros so this has been a bit challenging.
I have been stuck on this for a few days.

I am basically looking for a way to count the number of an item across multiple sheets.
For example:

Mysheets = list of all sheets in my workbook.
Say i want to count the number of times "P" is found in colum F on every sheet.

Something like this:

COUNTIF(INDIRECT(&MySheets&!F2:F200),"F")

Now I have tried making an array and using Ctrl+Shift+Enter.
But I either only return one value, as apposed to a count, or only the first sheet is ever searched.
Tried several formulas on this site, and everywhere else on the web. I don't quite understand all the excel syntax.

Surely i am missing something, here's as far as i got, and i'm not sure if all this is even correct:

INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!F2:F200"),"P")>0),0))&"'!F2:F200"),1,0)

Any help would be greatly appreciated.

THanks,
-Will

Hi Guys,

I have created an Excel template that the company sends out to suppliers to provide us with Quotations. I've added some VB in the Before Save module to prompt the user to do certain things. The problem I have is that I run another procedure that loops through a list of data and re-formats it using the template and therefore every time it tries to save a file the Before Save prompt pops up.

Is there any way that I can disable the macros on the template on opening using VBA in the second procedure ?