Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Excel: VBA userform is shown but not loaded/initialized even though it was first unloaded?

I have a Userform1 where the user can select between different
transactions. If the Clientbutton is clicked I hide Userform1 and show
Userform2, opens an excel workbook and enters some values. If the
Exitbutton on Userform2 is clicked I unload Userform2 and in the
terminate event I save the workbook and show Userform1 again.

So far so good ... it works fine ... the problem is when I then click
the Clientbutton on Userform1 a second time. Userform2 is shown
alright, but the workbook is not opened and I cannot click neither the
Exitbutton nor the Close (X in upper right corner) ... only solution
is CTRL+ALT+DELETE.

Any solutions?

Regards, Luisa

Code on Userform1:
Private Sub ClientButton_Click()
UserForm1.Hide
UserForm2.Show
End Sub

Code on Userform2:
Private Sub ExitButton_Click()
Unload UserForm2
End Sub

Private Sub UserForm_initialize()
ClientFile = ActiveWorkbook.Path & "Client.xls"
Workbooks.Open Filename:=ClientFile
End Sub

Private Sub UserForm_Terminate()
ActiveWorkbook.Close SaveChanges:=True
UserForm1.Show
End Sub


Post your answer or comment

comments powered by Disqus
When I develop a spreadsheet using VBA's on using Excel 2002 and then open
the spreadsheet on computer using Excel 2003 the VBA's don't work.... I can
see the code and edit it in the VBA editor, save the files and re-open it and
its there but when I go to run the marco the list of macros is all blank. If
I try to creat a name using a sub name in the program I have written the
Excel me there is a name conflict so it knows the sub exists! I have macros
enabled and security set to low to get them to work and that did not help
either. If I retype (using a different sub name) the same exact macro in the
same module it is are recognized and it works.

Going from Excel 2003 to Excell 2002 works fine.

SInce I am just learning learning VBA from scratch, having to figure out
things like this on myself has been mpossible. Any suggestions

Greetings,

I have a workbook created on Excel 2007 in which I added a TreeView control on a new Form. This was done via the Controls Toolbox: I right-clicked on it and selected "Additional Controls" and checked the Microsoft TreeView Control, version 6.0. (from the MSCOMCTL.OCX file)

But now, when the workbook is opened on a new installation of Office (Excel) 2010 Pro 64-bit, this error appears in a window called "Microsoft Forms":

Could not load an object because it is not available on this machine

Help??

RiTz21

I have made an Excel-VBA Project using a User Form with the DTPicker
Control.
Unfortunately this program does not work on the machines of the persons
who have to work with this program.
The Error Message: "Could not load an object because it is not
available on this machine" appears.

Can someone please help me to solve this problem?

--
H.A. de Wilde
------------------------------------------------------------------------
H.A. de Wilde's Profile: http://www.excelforum.com/member.php...o&userid=30679
View this thread: http://www.excelforum.com/showthread...hreadid=555514

Before Excel opens I get a dialog with "Microsoft Forms" in the title bar
with the following text: Could not load an object because it is not available
on this machine.

Dialog displays a second time after selecting OK. OK again and Excel is
available. Doesn't appear to cause any problems with using Excel

Why am I getting this? This just started recently and re-install of Office
2007 Enterprise and re-install of SP1 does not remedy situation.

I have made an Excel-VBA Project using a User Form with the DTPicker Control.
Unfortunately this program does not work on the machines of the persons who have to work with this program.
The Error Message: "Could not load an object because it is not available on this machine" appears.

Can someone please help me to solve this problem?

Thanks in advance for the help.

I am working on an excel/vba userform that can add date to my excel workbook. The problem that I have come up with is trying to add data to two separate excel sheets.

Explanation of form:
Used to track volunteer events and points of contact. One sheet has volunteer events listed on it, the other has points of contact listed on it. The goal is to use one userform to enter all new info for an event and the point of contact information for it, then add this and it will populate the next empty row in both events and poc sheets.

Code is below;
Private Sub cmdAdd_Click()
  
If Trim(Me.EventName.Value) = "" Then
  Me.EventName.SetFocus
  MsgBox "Please enter an event name"
  Exit Sub
End If

If Trim(Me.VolReq.Value) = "" Then
  Me.VolReq.SetFocus
  MsgBox "Please enter how many volunteers are requested or unspecified"
  Exit Sub
End If

If Trim(Me.HoursDay.Value) = "" Then
  Me.HoursDay.SetFocus
  MsgBox "Please enter how many hours long this event is from start to finish"
  Exit Sub
End If

If Trim(Me.PreviousYes.Value) = "" And Trim(Me.NewYes.Value) = "" Then
    Me.PreviousYes.SetFocus
    MsgBox "Please choose either previous or new POC and check the box"
  Exit Sub
End If

If Trim(Me.PreviousYes.Value) = "" And Trim(Me.NewYes.Value) = "" Then
    Me.PreviousYes.SetFocus
    MsgBox "Please choose either previous or new POC and check the box"
  Exit Sub
End If

If Trim(Me.PreviousYes.Value) = "True" And Trim(Me.NewYes.Value) = "True" Then
    Me.PreviousYes.SetFocus
    MsgBox "Please choose either previous or new POC and check the box"
  Exit Sub
End If

If Trim(Me.PreviousYes.Value) = "True" And Trim(Me.Previous.Value) = "" Then
    Me.Previous.SetFocus
    MsgBox "Please choose a previous POC or uncheck the previous box"
  Exit Sub
End If

If Trim(Me.NewYes.Value) = "True" And Trim(Me.FullName.Value) = "" Then
    Me.FullName.SetFocus
    MsgBox "Please write in the full name of the POC"
  Exit Sub
End If

If Trim(Me.NewYes.Value) = "True" And Trim(Me.Phone.Value) = "" Then
    Me.Phone.SetFocus
    MsgBox "Please write in the phone number of the POC"
  Exit Sub
End If

If Trim(Me.NewYes.Value) = "True" And Trim(Me.Email.Value) = "" Then
    Me.Email.SetFocus
    MsgBox "Please write in the email of the POC"
  Exit Sub
End If

Dim emptyRow As Long
With Sheet1
emptyRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(emptyRow, 1).Value = Me.DayStart.Value
.Cells(emptyRow, 2).Value = Me.DayEnd.Value
.Cells(emptyRow, 3).Value = Me.EventName.Value
.Cells(emptyRow, 4).Value = Me.VolReq.Value
.Cells(emptyRow, 6).Value = Me.MultShift.Value
.Cells(emptyRow, 7).Value = Me.HoursDay.Value

If Trim(Me.PreviousYes.Value) = "True" Then
  .Cells(emptyRow, 8).Value = Me.Previous.Value
ElseIf Trim(Me.NewYes.Value) = "True" Then
  .Cells(emptyRow, 8).Value = Me.FullName.Value
End If

End With

With Sheet2
emptyRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(emptyRow, 1).Value = Me.FullName.Value
.Cells(emptyRow, 2).Value = Me.EventName.Value
.Cells(emptyRow, 3).Value = Me.Phone.Value
.Cells(emptyRow, 4).Value = Me.Email.Value
End With

Me.EventName.Value = ""
Me.VolReq.Value = ""
Me.MultShift.Value = "False"
Me.HoursDay.Value = ""
Me.DayStart.Value = ""
Me.DayEnd.Value = ""
Me.PreviousYes.Value = "False"
Me.Previous.Value = ""
Me.NewYes.Value = "False"
Me.FullName.Value = ""
Me.Phone.Value = ""
Me.Email.Value = ""

End Sub
Any help is appreciated. I have tried scouring the internet for an example to follow but couldn't find one that fit.

Hi, i can't open forms, it says "Could not load an object because it is not available on this machine", and open vba code!What's the problem!HOw to resolve this?Plz Help....I have MS Excel 2003, Windows XP.

Oh Wise Ones,
I just started getting this error last week when Excel
starts. After I click about 9 OK boxes it will load and run. What have I done
and what can I do to correct it?

"Could not load an object because it is not available on this machine"
The title bar of the message box reads "Microsoft Forms"

Thanks,
Mike

Excel 2003
XP Professional

Oh Wise Ones,
I just started getting this error last week when Excel
starts. After I click about 9 OK boxes it will load and run. What have I done
and what can I do to correct it?

"Could not load an object because it is not available on this machine"
The title bar of the message box reads "Microsoft Forms"

Thanks,
Mike

Excel 2003
XP Professional

You folks have been great in helping me out to learn how to use VBA Code.
Not sure if what I need help on now, is VBA code or something that I can do
just from Excel, but here goes my question.

I have a VBA print button on say my Input Page sheet. This button is
pressed after information is completed in the above form. What currently
happens is the page that pulls information from my Input Page sheet, flashs
on the screen for a second or two. What I would like to have happen is that
sheet NOT to show even though the print button has been pressed. What I
would like to see, while this process is being printed is just my Input Page
sheet.

Steve

I just changed a few things on my application (added some modules and forms,
changed the code in some modules and forms). My client just encountered the
following problem on this new version. When he opens the application he gets
the following message
"Could not load an object because it is not available on this machine" under
"Microsoft forms" title
He doesnot get it with the previous versions of the application.
I do not get the message at all on my machine.
Do you have any idea where this could come from? (I checked all my forms and
they all work perfectly well)
Thanks a lot
--
caroline

Hi guys!

Is it possible to run a macro when a cell is clicked (with the mouse arrow) but NOT when is selected with, say, the keyboard?

i.e I want to run a macro when A2 is clicked but not when I change my active cell with the keyboard arrows.

thanks

How can I print something off of an Excel Spreadsheet when the document will
not open to begin with? It was first emailed to me as an attachment and I
can't seem to open it.

Hi,

I have seen another post in this forum from June mentioning Excel error reporting when closing workbooks, potentially caused by PDF software.

I have a workbook in which I have created several, very similar userforms, each of which has a single identical graphic, some labels and some listboxes. Consistently throughout testing the application I have found that Excel will create an error report on closing if any of 4 out of 8 of these userforms have been activated during the session. It is the same four userforms each time - if they are not activated the error report does not get created.

I have double checked my VBA and the structure (but not the variables) is identical in the error-generating and non-error generating userforms, so I can see no logical reason why this is occuring.

Can anybody shed any light on this for me?

Incidentally, I have Adobe Reader 7.0 installed, and a "print-to-PDF" type utility called "pdfFactory pro" which is standard in my company (i.e. I wouldn't be able to get rid of it even if I wanted to).

Much gratitude in advance of any help,
Andywiz

I've created an Excel workbook with several userforms and macros, and need it
to run on a Mac running Excel X. When I open the workbook in Excel X on the
Mac, and open the VBA Editor, none of my forms show up - as if the VBA
project contained no userforms at all - and executing the code to load and
show the forms causes an error.

I've read that the Mac version of Excel doesn't support ActiveX controls.
My question is: is there a way to build a userform in Excel VBA (in Windows)
using controls that aren't ActiveX controls? Or is ActiveX all there is for
building userforms in Excel VBA? I've seen people say to use the controls
from the Forms toolbar in Excel, but I don't want to add controls to the
workbook, I want to add them to a userform (if this makes any sense).

Thanks

Hi, Would appreciate any help with this:

I've been refreshing myself on VBA after a while away from it, and am trying some VERY basic VBA stuff in Excel.
OK, so I ...
1. insert a Userform in my project
2. It gets called Userform by default, so I want to change the name.
3. So I go into the userform properties and change the name, to let's say: usrTestForm
4. The properties window certainly shows that the name has changed.
5 BUT... If I now highlight the userform in the navigator tree, right click and select "view code"...
6. when I drop down the code object window, I only see code subroutines for "Userform".
7. And if I click on the Userform to generate a Click subroutine, when I go and look at the code, it has generated it for Userform_Click.
This is driving me crazy because I have tried it on two PCs, one running win 7 + Excel 2007 and the other Win 7 64b + Excel 2003 and they both do the same thing. It can only be something I'm doing wrong - or something common to Win 7, but I haven't the foggiest why...
It's like there's an "internal" name that it does not change even though it shows the changed name in the properties panel,

Thanks in advance!

Hello,

I found nothing helpful on the forum (well, there is this post but I would like to know if an alternative solution can be found) so I start my first post.

I am coding a function AAA which, at a point of time, needs to call an other function BBB to get an intermediate outcome.

My BBB function ends up by updating the RowSource propriety of a ComboBox belonging to the userform (lets say Form_BBB) from which it is usually called.


	VB:
	
 BBB() 
     
    Dim Range_BBB As Range 
     
    
 
     
    Form_BBB.ComboBox_BBB.RowSource = Range_BBB 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My problem is that when my AAA function is calling my BBB function I get an error because the Form_BBB is not loaded. To solve this problem, I would like to test if Form_BBB is loaded before updating the RowSource of my ComboBox_BBB, but without loading the form if it is not (so in cant use the Form_BBB proprieties).
It should be something like:


	VB:
	
 BBB() 
     
    Dim Range_BBB As Range 
     
    
 
     
    If  isLoaded Form_BBB Then 
        Form_BBB.ComboBox_BBB.RowSource = Range_BBB 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Does anyone can help??

Thanks

Hi everyone,

I'm building an excel database using VBA and have the following problem:

I've got 2 Userforms and the excel spreadsheet containing all the data.

The first Userform displays various information in textboxes and also contains a command button which when clicked, brings up the second userform.

The second userform also displays information from the database in text boxes.

My problem is that I want the second userfrom to Automatically input its data -i.e. without the user having to do anything...at the moment the best i can do is have another command button which launches my sub and fills in all the textboxes, which is OK but not ideal.

Is there a simple way for me to automatically launch my sub as soon as Userform2 is opened?

Thanks a lot,
Tristan

If I programmically import a UserForm with
Application.VBE.ActiveVBProject.VBComponents.Impor t ("UserForm.frm")
and show it with
Set UserForm = VBA.UserForms.Add("UserForm")
UserForm.Show
then change any control property (in my case,
UserForm.Label1.Backcolor) in a module (rather than from within the
UserForm code), the UserForm initialize event is triggered on the
first call to the module (not on subsequent calls). Also, the control
property is changed (Backcolor), but the screendisplay doesn't reflect
this (Application.ScreenUpdating=False is not used). As expected, all
control property changes done within the UserForm code behave
properly.

This is for XL97 on Windows 98

Thanks for any help.
Dave G

UserForm Code
/// works as expected
Private Sub Label2_Click()
Static lb2Cnt As Integer
Dim IsOdd As Boolean

lb2Cnt = lb2Cnt + 1
If lb2Cnt / 2 = Int(lb2Cnt / 2) Then IsOdd = False Else IsOdd =
True
If IsOdd Then UserForm.Label2.BackColor = &HFF& Else
UserForm.Label2.BackColor = &HFF00&
End Sub

///doesn't work even though the same code (expect use Label1) as above
is in the Call routine
Private Sub Label1_Click()
'change via Call
Static lb1Cnt As Integer
Dim IsOdd As Boolean

lb1Cnt = lb1Cnt + 1
If lb1Cnt / 2 = Int(lb1Cnt / 2) Then IsOdd = False Else IsOdd =
True
Call TestViaCall(IsOdd)
End Sub

Hello,

I am fairly new to VBA and macro programming. I would like to devise a
macro that processes a bunch of workbooks. I would like to create a
userform such that I can drag and drop workbooks from windows explorer
into a listbox to determine which files I would like to process.

I found tons of examples online of how to devise a VBA program to
accept "drag and dropped" files from windows explorer, however when I
try to do this from within an excel vba program, I find that the
listbox is slightly different and does not contain the odeDragDrop
functionality.

Is there some kind of library or something I need to import into my
excel vba program? I hope I am making some sense... please help!

Is there anyway to have Excel via VBA do a repetitive calc while a userform
is showing? So when I say:

Userform.Show

I want to mimic what would happen if I held down the F9 key. Now this could
either be based on:

1) a Loop with a timer
2) or it could be continous (triggered after the .show Method) and disabled
when I press a command button on the shown userform.

Any ideas?

Thanks

EM

When I open an Excel Application via VBA the Analysis ToolPac functions are
not loaded. (Specially I want to see the function "EDATE" but there are
others nice functions.)

VBA:
Dim appWB As Excel.Application

Set appWB = CreateObject("Excel.Application")
appWB.Workbooks.Open Filename:="c:mydocsmyXLWB.xls", ReadOnly:=False
appWB.Visible = True

Even when I set the addins property to True they are not recognized:

'Install Analysis Tool pack
AddIns("Analysis ToolPak").Installed = True

Any Suggestions?

Thank you,
Mark

Hi All,

I have a question that might not be suited to this forum but I have an Excel VBA Userform that is a collaboration between different sources... some records are manually entered into the form and others a copy & paste between a pdf or text file into the form...

Question is, is there a property in the textfield control that allows an autofill style of copy and paste so that all textfields on the form are "linked" so that the datafields for that record can be copied and pasted into the form at once and where there is a paragraph character or carriage return, data is flowed onto the next field... the datafields are of variable length and I have tried setting the textcontrol properties to AutoTab is True and EnterKeyBehaviour is True... plus I have all the textfields in tab order.

This would save a fair amount of time, at the moment it is copy each datafield one at a time and paste into the corresponding textfield on the form... there are approx. 50 fields on the form...

Sorry if there is another post out there like this - everytime I search for a few words I get a memory error for the search.

Thanks in advance

Hi

I am trying to create a userform containing 3 check boxes.

Imagine this scenario:
*Pretend at the end of a school year I wanted to create a database regarding
9 pupils’ homework completion.
*There have been 3 homework assignments.
*I want the database to be 4 columns by 10 rows – A row for each pupil and a
column for each subject.
*For each pupil I want to use 3 checkboxes to input whether or not he/she
handed his/her assignments in on time. If I leave a checkbox blank, I want
Excel to put FALSE in the cell corresponding to that pupil and that
assignment. If I tick a checkbox I want Excel to put TRUE in the
corresponding cell.

I realise that there may be simpler or more efficient ways of doing this
(including manually) than using checkboxes….I have just tried to think of a
simple example.

I have tried to create such a form but encountered the following problems:
*When I want to leave a checkbox blank to produce FALSE and I tick the
following checkbox – Excel puts TRUE in that first checkbox’s corresponding
cell instead of in the second checkbox’s cell.

A solution to this I thought was:
With each checkbox’s code, add code at the beginning that selects the
appropriate cell in the active row.
I have tried guessing code to do that but none of the following have worked.
For example if I want data about the 3rd assignment to appear in the 4th
column
ActiveRow.Cells(4).Select
ActiveRow.Column4.Select
Range(,4).Select
Range($4).Select

Another situation
I want to use a scroll-bar. I want to be able to see the value of the
scrollbar as I drag it.

I thought the solution was to add a label and set that label’s caption to
the scroll bar's value

Private Sub HSBrating_Change()
LBLrating.Caption = HSBrating.Value ‘A label called “rating”’s
caption = scroll bar value
ActiveCell.Value = HSBrating.Value
ActiveCell.Offset(0, 1).Range("A1").Select
End Sub

2 problems I have faced with this are
*It doesn’t display which value the scrollbar is at whilst you’re dragging it
*As a result you have to let go of the mouse to see what value it is at, and
when you do that it inserts that value onto the spreadsheet – which I don’t
want. I don’t want it to insert a value onto the spreadsheet and move the
cursor until I have selected the value I want.

Could someone please help me with solutions to these. I’m especially
interested in learning about that checkboxes problem.

Thanks

Phil


No luck finding an answer? You could always try Google.