Thanks to Stephen Bullen for fixing VBA's miserable UserForm.
With the qualification that I'm only using Win 2000 and this might've
been fixed, and if I understand UserForm correctly:
The seeming fact, which I just learned, that the highest-level visual
control in VBA, UserForm, by design fails to implement even the most
basic Windows features of being resizable, min- and maximizable (and I
have yet to see a ScreenUpdate), has got to be the most pathetic, half-
assed thing I've seen out of Redmond in a long time.
UserForm is not a FORM. It has neither the appearance nor
functionality of a form. It has little more than dialog functionality.
I mean, when's the last time you've seen something on your Windows
screen that's missing the Min/Max/Restore buttons, for God's sake?
While they were at it, why didn't they omit caption functionality? Or
force UserForm to be modal? (Thank God they didn't, but now I wonder:
why not?)
But can someone answer: what do users do who have screen resolutions
different from the programmer's, when the box he designed doesn't fit
their screen? As happened to me today when I ported my NonUserForm
from one PC to another. I had to change in the form size IN DESIGN
MODE.
And, how in the world can all you programmers write an *application*
in VBA?
I REALLY hope I'm seriously missing something in my understanding
here, I really do. It sure wouldn't be the first time. In fact, crow
is one of the dishes I eat most gladly.
***
In the meantime (and I can't believe it's taken me this long to
discover it), I took Peter T's advice and went to Stephen Bullen's
fabulous Excel site:
www.oaltd.co.uk/Excel/Default.htm
where there are all kinds of goodies. Not least of which is
FormFun.xls, which includes all the necessary hooks out to Windows to
fix the miserable, decrepit piece of crap that is UserForm.
[Rant alert!]
I have said this for decades: Microflot's Soviet approach to things
will be its undoing. Because soon a company is going to come along
with a computer on which you don't click a button called 'Start' to
shut the goddamned system down.
And by Soviet, I mean: Refusing to fix design flaws and their
thousands of points of failure to implement their own--excellent--
Windows paradigms in EVERY user control, on EVERY screen, in EVERY
context across their ENTIRE product spectrum.
Yet, accomplishing that feat between even the most intimate siblings
of the Office family is absolutely beyond this company.
Instead, where do they spend their computing resources? Migrating us
to operating systems requiring multiple cores and multi-gigabytes of
storage, so we can have the thrill of executing a few MISERABLE
machine instructions to do the things we did pretty well on our old
1981 IBM PCs--like balancing our checkbooks--but with 3D graphics,
animation, and an electrical draw of a couple hundred more watts.
If you have shares in MS, I say: maybe don't keep them for long.
*** Tag: Can i link various text in a cell to a picture Tag: 981209
Excel 2007 - How do I ...
bring data into an excel 2007 worksheet that is stored in a sql database table?
The idea is to parameterize the "where" clause in the sql select statement
limiting the result set.
There are lots of examples in the literature with regard to bringing in data
stored in cubes --- but not so much from data in sql table.
The basic issue is I want bring in descriptive information about say a
contract (things like description, signing date, etc). Cubes which most
(all) of the literature deals with are dealing with aggregations of amounts
or "measures" and not so much about descriptions.
As always all suggestions are welcome.
Mark Tag: Can i link various text in a cell to a picture Tag: 981199
UserForm ShowModal Problem
I have a UserForm that will not Unload correctly for some reason when
ShowModal = True. I get a Run-time Error '91': Object Variable or With
block variable not set. But when I change ShowModal = False then everything
Unloads properly. I have 8 other UserForms that have ShowModal = True and
they all Unload just fine. Anybody know why this happens?
Thanks in Advance,
Ryan Tag: Can i link various text in a cell to a picture Tag: 981193
code to extract data from a website runs of XP but not Vista
I am working on code to extract data from a website. I am using
CreateObject("InternetExplorer.Application") to start IE. The code is far
from working perfectly but when I moved the code from an XP computer to a
Vista computer I canâ??t get past the â??Do Until .readystate = 4â?? statement.
IE opens visible and I get the following error message:
â??The object invoked has disconnected from its clientsâ??
I did not get this error on the XP computer.
The code is the following:
Sub Tester()
Dim objIE As Object
Dim allTabs, t, x As Integer
Dim r As Long, s As String, c As Long
Set objIE = CreateObject("InternetExplorer.Application")
With objIE
.Visible = False
.Navigate "http://www.masters.org/en_US/scores/index.html"
Do Until .readystate = 4 â??hangs on this statement on the Vista computer
DoEvents
Loop
With .document
Set allTabs = .getElementsByTagName("TABLE")
For Each t In allTabs
'table with scores is always greater than 30 and only table that
large on page
If t.Rows.Length > 29 Then
r = 2
For x = 1 To t.Rows.Length - 1
For c = 0 To 9
s = t.Rows(x).Cells(c).innerHTML
ActiveWorkbook.Sheets("Info").Cells(r, c + 1).Value
= s
Next c
r = r + 1
Next x
End If
Next t
End With
End With
End Sub
I have a feeling from looking up the error on the web that it may have
something to do with the added security of Vista. Has anyone else run up
against this problem? I see a lot of code samples here that use the same
code to open IE that I do.
--
russ Tag: Can i link various text in a cell to a picture Tag: 981192
Automanipulating IE drop-down list in VBA/Excel
I'm strictly a newbie in VB; I've been programming on the mainframe for
decades but am only recently come to VBA, VBScript etc, so if you notice that
I'm going about this all wrong, be sure and point me in a better direction.
I have a spreadsheet that contains data from the web, and I'd like to make it
smart enough to update itself when I tell it to. I've learned a little about
the "Document" object model, and have gotten to the point where I can browse
down through all the .Document.All items and figure out which ones I want VBA
to manipulate. I can even fill in the values of web pages' text boxes and
simulate a .Click on its buttons.
But now I want to make a selection from a drop-down box. Let's say it has a
list of states on it: I want my VBA program to pick out the correct state
and convince the web page that I've chosen it. I've found the Option items
that define the possible selections, and I see that if I find the correct one
and change its Selected property to True, it shows up on the page correctly
as though I selected it myself.
But what the page doesn't do is proceed from that point as though I had
clicked on the item - that is, it shows up in the drop-down selection box,
but I can't get it to go forward from there. I've tried .Clicking on
everything I can think of (might have missed one, though), but nothing seems
to work. What am I supposed to do to get to the next step.
If there's a FAQ on this, or another forum I should post to, just point me
in the right direction. And if there's an easier way than using the Document
object model - which wouldn't surprise me - feel free to point that out to
me, too. Thanks. Tag: Can i link various text in a cell to a picture Tag: 981190
Create a Macro with a List
Hi Experts:
>
> I would like to build a macro with the List for multiple individuals that
> will then enter the data from the List to the Summary Worksheet in the same
> Workbook. This is the example of what I want to do....
>
> List Sheet
>
> John Jones in Column A....123456 in Column B...ABCDF in Column C and 90.05%
> in Column D
>
> Put that data on the Summary Worksheet in Columns B, C, D & E
>
> I would also like to remove the % sign in Column D
>
> Can anyone help me with this? I would appreciate any assistance you can
> provide. I'm working on a new reporting document that is using VLOOKUPs....a
> lot of them every month....I think the Macro would be more efficient, but I
> need your help to build on.
--
jeannie v Tag: Can i link various text in a cell to a picture Tag: 981188
I am not sure
Hello;
I am trying to help someone, for which I am not sure there exists a
solution. He has a .csv file that he is opening in Excel. The values,
somehow, import into the correct conglomeration of data. More in a minute.
Column A is a number formatted as text. Column B is a series of numbers,
some formatted as text. Those that are not formatted as text, are separated
by commas (Huh!!) and hyphens.
The object is to concatenate the two values together. Example:
A | B | C
| |
1 | 357 |Argentina
| |
345 |32,34,39 |Paraguay
| 40-45 |
| |
33945 |27-30,41, | USA
|43, 45 |
The data should finally look like this for USA (etc.)
A | B
3394527 |USA
3394528 |USA
3394529 |USA
3394530 |USA
3394541 |USA
3394543 |USA
3394545 |USA
Which column the new values end up in does not matter. I know how to do this
fairly rapidly with formula's and a big enough worksheet. Then copy and paste
the values to where I want them.
I am wondering if this is even possible. I am not a big VB fan. I like to
avoid them if I can, but I wonder if this can even be handeled by VB. Column
B on one of the cells looks like this as an example.
264235, 264469, 264476, 264497, 264536-264539, 264543, 264581-264584,
264724, 264729, 264772-264773
There is no regularity to the number of digits. There is no regularity to
the hyphenation location. There is no regularity to the format of the numbers
(which I guess all could be changed to numbers and back to text for
concantenation) and there is no regularity to the quantity of values in the
cell.
Is there any hope for this? Any help would be appreciated.
Thank You
Frank Pytel
PS. I see a lot of posts here that look really nice. I am on the Office
Online site. I know these posts can be viewed and edited at other sites. Can
I set this up in Outlook so that I can Post and search. Kind of like Outlook
Express? Thanks Tag: Can i link various text in a cell to a picture Tag: 981185
How to execute macro based on a users computer settings
Hello experts,
I'd like to find a method/script to only execute an excel macro based
on a users existing file listing on their computer ( or it could be a
pre-existing setting; ie. their IP or MAC address). Ideally I'd like
to have a macro check for the existence of a certain file (or password
phrase encoded in the file) on the directory of a users computer, if
YES then execute macro, ELSE don't do anything. This is all done in
the background right before the event that usually triggers the
primary macro, which is an ftp command line script.
My guess is creating a batch file that executes in a windows shell and
outputs a value that could be read, but I'm not sure if that's the
best way to achieve what I want.
I want to avoid using a pop-up form and password inside the excel file
- this is currently what I already have.
Thank you for your help,
-Doug Jr. Tag: Can i link various text in a cell to a picture Tag: 981182
How best to break up code
I hit the "procedure too large" error and I can't seem to get past it.
I'm well aware of the reason and I can tell you know that I've
effectively crammed all the IFs and LOOPs into it that I can, so let's
assume that the code can't be condensed further. I've spent half-a-day
reading through this group and Googling and can't seem to make it all
come together.
So my question is, how do I create this "well structured code" when I
have SO MANY declarations which need to be used across the different
procedures? I don't want to pass all of my declarations as arguments
as I feel like that would take forever. There has got to me a more
logical way that I am missing. And functions are supposed to be small
aren't they?
In reading the vba help, it seems that declaring the procedure or
perhaps the individual variables using "static" would allow them to
persist but they don't. Does it make a difference if I put everything
in separate modules? From what I've read that isn't necessary.
I read somewhere in this group that someone had a procedure that held
all of their variables and they just called it in each procedure. How
would this have been done? And if the variable is changed in the first
procedure that calls it, then how can I get that value to persist to
the next?
I know the first question is going to be, let me see your code. But
because this is more of a conceptual question, I'm leaving it out for
now. I'll gladly provide some as needed.
Many thanks for any assistance.
Shelton Tag: Can i link various text in a cell to a picture Tag: 981179
VBA Additional controls
Hi all,
I wanted to insert a spreadsheet on a form in excel 2003. So I went to tools
additional controal and added spreadsheet 11.0. When I try and use this in
my excel 2007 it says it can not find the object . In the additional controls
there is no spreadsheet 11.0 is there way to add it ? a.so how can i find out
what all the other additional controls do.
Thanks Tag: Can i link various text in a cell to a picture Tag: 981175
Controlling how a second excel file opens
I have an Excel workbook I use to aggregate data from other excel workbooks.
My macros copy data from the secondary workbook and appends the data to the
primary workbook. My question:
Is there the capability to open up secondary workbook without the secondary
workbook executing any of it's macros and not do any calculations?
Is there a better way to pull data from another excel workbook? Links are
not an option.
Thanks,
- Pat Tag: Can i link various text in a cell to a picture Tag: 981174
Checkbox
I created a Userform with several checkboxes and comboboxes. Once all the
information is filled out, i hit the run button. I'm getting an error
because it's not reading if the checkbox is marked or not, it's always read
as unmarked. What is the correct way of coding this? Tag: Can i link various text in a cell to a picture Tag: 981162
Running Macros Remotely (.tcl file)
I am trying to set up an automated macro routine via a .tcl "tickle"
file (scheduled by the windows scheduler) to execute at a set time
each day. When I execute the .tcl file below it will open the excel
file but it errors out when it tries to execute the macro
Error Msg : The macro may not be available in this workbook or all
macros may be disabled.
I can run the macro with no issues manually from the excel file.
1) does anyone have any idea what might be wrong ?
2) is there any other way to automatically run the macros at a given
time ?
Thanks, Grant
TCL script:
set application [::tcom::ref createobject "Excel.Application"]
$application Visible 1
set workbooks [$application Workbooks]
set workbook [$workbooks Open "D:/Profiles/r33998/Desktop/Working
Parts Tracker/Split Out Tracking System/TCL testing.xls" 0 FALSE 2
sourcing]
$application Run "TCL testing.xls!protect_worksheets_"
#$workbook Save
$application Quit Tag: Can i link various text in a cell to a picture Tag: 981154
Combo Box and smart key selection
Hi
I have a spreadsheet (windows xp, MS Office 2003) with several
userforms. One of these has a combo box, populated from the following
code:
Private Sub UserForm_Initialize()
Dim myArr As Variant
With Me.cbStdOP
.ColumnCount = 9 'columns A:I
.ColumnWidths = "180;0;0;0;25;0;0;0;0" 'hide the last 8
columns
End With
With Worksheets("AutoEntry")
myArr = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) _
.Resize(, Me.cbStdOP.ColumnCount)
End With
Me.cbStdOP.List = myArr
End Sub
This shows in the combo box only the column A (description) and column
E (numerical codes) entries from the 'AutoEntry' worksheet. The user
scrolls thru these and selects from these choices in the combo box,
and the resulting data is placed into another ws ('Operations'). The
rows in 'AutoEntry' are sorted by column E (numerical codes).
I know this is not the best way to do this. I would like the user to
be able to type the first numbers of the codes and have the combo box
go to that selection (there are > 1500 rows to scroll through on the
AutoEntry ws).
Ideally, there would be a radio button to search by description OR by
numerical code, and the combo box would show the matches.
Thanks for any guidance!
cls Tag: Can i link various text in a cell to a picture Tag: 981153
Working with Arrays
I am trying to use an array to move a group of sheets from one workbook to a
new workbook. I know how to move them ALL at once to a new workbook using:
Sheets(array("Sheet1", "Sheet2", "etc.")).Move
However, only want to sent select sheets (selected by another, working,
section of code). Here is my code, so far:
' define and setup variables
Dim create_array, all_graphs
Dim offset_var As Long
Dim array_counter As Long
offset_var = 0
array_counter = 0
' this is a list of all the sheets that CAN exist
all_graphs = Array("1.1.1 - LD AN Consumption", "1.2.1 - AN Amounts", "1.3.1
- Total AN Consumption", "2.1.1 - Tank Temperature", "2.1.2 - Fudge Point",
"2.1.3 - pH", "3.1.1 - Blender Speed", "3.1.2 - Hot Cup Density", "3.1.3 -
Viscosity", "4.1.1 - Truck Cup Densities", "4.2.1 - PE105 Calibration - AN",
"4.2.2 - PE105 Calibration - Emu", "4.2.3 - PE105 Calibration - FO", "4.3.1 -
PE106 Calibration - AN", "4.3.2 - PE106 Calibration - Emu", "4.3.3 - PE106
Calibration - FO", "4.4.1 - PE105 - Scale vs. Load", "4.4.2 - PE106 - Scale
vs. Load", "5.1.1 - Tank Temperature", "5.1.2 - Fudge Point", "5.1.3 - pH",
"6.1.1 - Blender Speed", "6.1.2 - Hot Cup Density", "6.1.3 - Viscosity",
"7.1.1 - ANFO Test", "8.1.1 - Calibration - Unit 8018", "8.1.2 - Calibration
- Unit 8025")
' there are a total of 27 potential sheets, however, only the ones with a
value of 1, found at range_create.Offset(offset_var, 0), will actually exist
While offset_var < 27
If range_create.Offset(offset_var, 0).Value = 1 Then
create_array = all_graphs(offset_var + 1)
End If
offset_var = offset_var + 1
Wend
' move chart sheets to the end of a new workbook
Sheets(create_array()).Move
The section of my code that doesn't seem to work, at all, is
Sheets(create_array()).Move. How do I implement this properly, or is there a
problem with the previous section? I originally had it say:
If range_create.Offset(0, 0) = 1 Then
Sheets(Array("1.1.1 - LD AN Consumption").Move
End If
' etc... all the way to the last one, but it only worked for the first one,
and then crashed, it didn't want to add it to the same "new" workbook or
another new one, which wasn't my needs anyways
Thanks you Jon Peltier, Peter T., Dave Peterson, and ND Pard for all your
help on that last section of code. I am still stuck working on it, but it is
not necessary to complete at this time Tag: Can i link various text in a cell to a picture Tag: 981148
Selfcert no longer working correctly
I have used Selfcert successfuly for years. Recently it started asking
to 'Enable Macros each time it was opened. I went to VBA Tools ->
Digital Signature -> Choose and my signature when I chose it would
not stick. Then I removed it and tried to use Selfcert to get it working
again. Each time I get he message 'An error occurred during certificate
creation. Selfcert was unable to create your certificate.'. It does this
every time. I cannot find any reason for it, nor find any possible
solution.
TIA
--
Gus Evans Tag: Can i link various text in a cell to a picture Tag: 981146
Check access right to folder
Hi
Actually an easy one but I can't find anything on google. How can I
check the access right to a folder (I just want to know if the user of
the macro has access to a shared folder)? Is there any function that
does that? (that would be on a network drive)
thanks in advance
Charles Tag: Can i link various text in a cell to a picture Tag: 981143
Save Date
How can I update the value in a cell that represents the last date the
workbook was saved? Tag: Can i link various text in a cell to a picture Tag: 981137
Userform: what happen to the sub who launched it?
Hello
quick question on userforms. If I have in the following sub something
like
sub LaunchUserform
load userform1
userform1.show
end sub
what happens to the sub once the userform has been loaded and appeared
on the screen? Is the sub terminated? If not at what point does the
sub resumes?
Same thing when I want to unload the userform, from a sub within the
userform. Let's say that in one of the procedures contained in the
userform I have:
sub DoTheStuff
if me.TheValue=1 then
unload me
end if
msgbox "ok"
unload me
end sub
I have the feeling it doesn't always unload the userform after the
unload me, and that it doesn't interupt the sub DoTheStuff at unload
me (I am working on a much more complicated macro, so it's not as easy
to spot what's going on). How can I say both unload the userform and
exit the sub at the same time?
I don't know if this is related but I am also using a userform close
sub which closes a DAO link and cleans up:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
Is there a chance that this is interfering with how the userform can
be unloaded?
The reason I ask is that sometimes when the userform gives the feeling
it is done and gone, it looks like VBA is still active and the link to
the DB not close (and I can interrupt the code by using the Reset
button in VBA).
So my ultimate question is something like is there an ultimate/propper/
ultra clean syntax or steps to follow to deal with userforms and
loading/unloading them properly?
If anyone has some lights on the topic, would be greatly appreciated.
Thanks in advance
Charles Tag: Can i link various text in a cell to a picture Tag: 981127
Excel VBA Query Across Databases
I'm trying to populate a recordset with 2 tables from seperate databases. So
far, I get no error when I run the query, but it's also not populating the
field from the second database (which happens to be the one I'm actively in).
The query is below:
With comboPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open "select strName from db1_name..TblAnalysisOptions AO join
db2_name..TblResultHeader RH on AO.guidAnalysisOptions=RH.guidAnalysisOptions"
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset comboPubs
' Tidy up
.Close
End With
Is there anyway to do this? Both our on the same machine. Tag: Can i link various text in a cell to a picture Tag: 981117
Copy rows from multiple workbook into a different workbook (sheet)
Please I am very new to Macro. Please Help me on this one. I will really
appreciate it. Will like to copy multiple rows from different workbook into
one sheet in a different workbook. Each row has the same headings.
How do i go about about to use macro. Again I haven't used it before. Please
pardon my ignorance.
Thanks for your help Tag: Can i link various text in a cell to a picture Tag: 981113
Staff resource query
Hi,
How can I use the following input to populate the "Staff Count" as
shown in the example below.
Start Finish
Person1 06:00 08:00
Person2 07:00 09:30
Time Staff Count
06:00 1
06:10 1
06:20 1
06:30 1
06:40 1
06:50 1
07:00 2
07:10 2
07:20 2
07:30 2
07:40 2
07:50 2
08:00 1
08:10 1
08:20 1
08:30 1
08:40 1
08:50 1
09:00 1
09:10 1
09:20 1
09:30 1
kind regards,
Matt Tag: Can i link various text in a cell to a picture Tag: 981112
Follow Hyperlink in Cell
I have a 3 column range on a sheet.
The first column populates a combobox on a form.
The 3rd column is a hyperlink to subfolder on a server.
Example: X:/MRASpreads/123Forms/Amendments
When I access the sheet itself and click on the cell with the hyperlink,
everything works
fine and I don't get any warning messages. Explorer just opens up in that
subdirectory.
I'm trying to get explorer to open up via code.
The following works:
ActiveWorkbook.FollowHyperlink Address:= _
Application.VLookup(ComboBox3.Value, Range("FullLocn"), 3, False), _
NewWindow:=True
The problem with the above is that it displays the virus warning message
before
opening up explorer. I've read that the warning can't be disabled but since
this
can be done directly from the worksheet, there must be another (better) way
to
do this than the way I'm trying to it.
Any ideas?
Thanks,
John Tag: Can i link various text in a cell to a picture Tag: 981110
Comparing 2 sheets
I have combined two files into one file. I used copy/paste to enter the
contents of file 2 into a new sheet on the original file. I now have a single
file which contains 2 sheets. Let's name the sheets: Source (smaller) and
Original (larger).
Now, I want to use column A containing 419 rows of the Source sheet to search
column A containing 4,763 rows of the Original sheet and highlight the ones
that matching names.
Example: "ABC Industries" is found in column A on both sheets - but in
different rows. On the source sheet, it is in row 5. On the original sheet,
it is in row 20.
I want to use the source sheet "ABC Industries" in row 5 to highlight the
"ABC Industries" in row 20 on the original (as it is a match). However, I
want automate the process of looking at all 419 names in the source sheet to
highlight the matches found in the original sheet.
How can I do this?
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200805/1 Tag: Can i link various text in a cell to a picture Tag: 981107
Text Box - Initial Content
Greetings
I am using Excel 2000
I have a spread sheet with columns holding Date, Order No., Name, etc
I wish to generate a user form which will display data from a selected row,
i.e. the entry's date Order No., Name, etc in Text boxes.
This will permit the user to simultaneously review and edit the data in a
reduced error manner.
I can so far only get a text box to display the existing content of a
specific cell, eg. "C15".
How do I get it to display the data from appropriate cell of the selected row.
So far the Control Source field associated with the Text Box has rejected
all my attempts.
So far I have defined the appropriate cells as a Range. Tag: Can i link various text in a cell to a picture Tag: 981101
Application.Run
Hello,
I have been looking at Application.Run
I see the following code in MSDN.
In this example they use a template name.
Does that mean a template can be not opened? And it will run a method from
any template?
How about an add-on?
how about an xla?
can you have:
Application.Run MacroName:=<<xla>>& "." _
& <<module1>> & "." & <<some method>>
??
strTemplate = InputBox("Enter the template name")
strModule = InputBox("Enter the module name")
strMacro = InputBox("Enter the macro name")
strParameter = InputBox("Enter a parameter value")
Application.Run MacroName:=strTemplate & "." _
& strModule & "." & strMacro, _
varg1:=strParameter Tag: Can i link various text in a cell to a picture Tag: 981099
VB/Excel query
To assist with office management, I need a VB script or excel macro that does
not allow input in a defined column if the data being input is not listed on
another column.
For ex: the file has a sheet marked Operator Name, which lists names of all
the staff members. The other sheet is an input sheet which requires us to
input the name of the staff and their performance details.
I need a VB script/excel solution that does not allow the inputter to input
a name that is not listed on sheet marked Operator Name. (Data validation or
List would not be appropriate as the team size is large and the inoutter will
have to scroll through a huge list of names).
Also, if I am not being too greedy, could it be possible for the script to
mention the closest possible name available on the Operator Name sheet in
case the inputter mis-spells the name.
Thank you for your assistance.
Regards,
PD. Tag: Can i link various text in a cell to a picture Tag: 981098
Compile Error in Hidden Module: Module 1
I have a workbook that has code that works on my computer, and a couple of
other computers. However, when the workbook is emailed to someone, then
emailed back, I get the compile error above. I originally had the code
password protected, but have since removed the password thinking that could
be the problem - but that didn't work.
the one thing I did notice was that the computer to which the file was sent
has Windows SP3 installed, while the computer getting the error on return has
SP2 installed. Could this have anything to do with it?
Does anyone know to what "Hidden Module" refers to if the code is not
password protected?
Thanks Tag: Can i link various text in a cell to a picture Tag: 981082
Variable length named ranges
I need a way to specify a variable for the last row in a named range.
A static definition looks like this:
ThisWorkbook.Names.Add Name:="dAge", RefersTo:="=RawData!$H$2:$H$100",
Visible:=True
I would like to replace â??100â?? with â??nRowsâ?? and need help with the syntax.
Thank you. Tag: Can i link various text in a cell to a picture Tag: 981077
ms excel 2007 Filesearch
I have the following code developed in Excel 2002. I have noted from
this group that .Filesearch is not longer supported in ms excel 2007.
I would like for this code to search for text "budget" in all
workbooks and return the file and it's path to either a listbox or
compile a list of all of the files names and path into an Excel
Workbook.
I am kind of lost on reworking this code with VBA in MS Excel 2007.
Any suggestions you have would be greatly appreciated
Sub findfiles()
With Application.FileSearch
.NewSearch
.LookIn = "C:\"
.SearchSubFolders = True
.TextOrProperty = "Budget"
.MatchTextExactly = False
.Filename = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count
UserForm1.ListBox1.AddItem.FoundFiles (i)
Next i
End With
UserForm1.Show
End Sub Tag: Can i link various text in a cell to a picture Tag: 981070
Math Operation: Set Subtraction
I have two range objects, and I want to subtract range B from range A. That
is, I want the opposite of Application.Intersect. This match concept is
illustrated at http://www.mathwords.com/s/set_subtraction.htm
I don't believe there's a single command for this, but can anyone provide
some thoughts? I plan on approaching this using a loop,
Application.Intersect, and Union() commands.
Thanks,
Matthew Pfluger Tag: Can i link various text in a cell to a picture Tag: 981064
Select Column based on Sheet Name
I have a macro that will copy and add a sheet, and name it for each name in
the first row.
I now need to select the column in each sheet where the sheet name and
header name are the same and set the background to gray. The number of
columns and rows will vary.
Is this possible to do?
Thanks for all your help. I use this site a lot. Tag: Can i link various text in a cell to a picture Tag: 981061
Help with Grabbing Cell Value from Range
This is the code I have:
' Grab the database name
Dim strDb As Range
Set strDb = Worksheets("Dashboard").Range("C2")
Dim strDBString As String
Set strDBString = strDb.Text
The range is assigned without a problem; however, extracting the actual text
value seems never to work. Can anyone clarify exactly how this should happen?
My underlying motivation is to pull a cell value in VBA, and then use that
in a SQL query within VBA as well. Tag: Can i link various text in a cell to a picture Tag: 981055
Sheet Protection Question
Greetings,
I have a worksheet with linking formulas in 79 cells (named ranges),
Toolbars>Control Toolbox>CommandButtons and a Toolbars>Forms>ComboBox.
This worksheet is set up as a form to view individual records chosen
from the ComboBox. This form is setup to do one of two tasks. First
view the record. And second, edit the record.
To view: I need to only see the information in the record and not be
able to change anything - until I click the "Start Edit" button.
To edit: All of the formulas are changed into values. Then I can edit
any or all of the cells.When finished, I click the "End Edit" button,
which saves the edited version of the record into the "Customer List"
sheet and then puts the formulas back into the cells.
The only thing I am missing is how to keep from overwriting the
formulas (in the view mode) with vba. The problem I ran into with
Protect was that the CommandButtons were also protected and I need
them to always be unprotected. Is there a way to lock everything but
the CommandButtons and the ComboBox?
I'm not even sure if protection is what I need. Security is not an
issue here. Overwriting the formulas is.
Another possibility would be if when the user entered any of the cells
on that page, to automatically trigger the edit mode. The cells all
have names (pfCell_4 thru 79 - eg. pfCell_4, pfCell_5, ... pfCell_79).
Any help will be greatly appreciated.
-Minitman Tag: Can i link various text in a cell to a picture Tag: 981054
Tracing where spreadsheet is opened from
Dear All
I am currently opening an existing Excel file from a macro within
SolidWorks (thus written in VBA). Is there any way that when the
spreadsheet opens, it checks to see where it was opened from? If it is
opened from SolidWorks I would like it to launch a certain macro
within the spreadsheet.
Many thanks in advance
Andy Tag: Can i link various text in a cell to a picture Tag: 981053
Retrieving the values of Excel check boxes in .NET
I have an Excel spreadsheet which contains multiple un-grouped check boxes
(from the Forms toolbar) which are not linked to any particular cell(s). I
want to read their values in .NET code. For this I'm using 'Microsoft Office
Interop Excel' and 'Microsoft Vbe Interop Forms' libraries. This is the code
I'm using:
String excelFile = "C:\\FileName.xls";
Excel.Application estimate = new Excel.Application();
Excel.Worksheet workSheet = new Excel.Worksheet();
estimate.Workbooks.Open(excelFile, 0, true, 5, "", "", false,
Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
workSheet =
(Excel.Worksheet)estimate.Worksheets.get_Item("WorksheetName");
Excel.CheckBox cb =
(Excel.CheckBox)workSheet.Shapes.Item("CheckBoxName").OLEFormat.Object;
cb.Value returns the value of the check box (the value is -4146 if the check
box is unchecked, 1 if it is checked and null if it is mixed).
At some point in time I was able to see the names of all the check boxes in
my worksheet (they were named "Check Box 1", "Check Box 2" and so on). But
now I see that all the check boxes have the same name "CheckBox" which is
very strange. This is making it impossible for me to retrieve the values of
the check boxes by specifying the check box name. I know I can use the index
also but it poses a similar problem - 'how do I know the index of each check
box?'. Coming back to the name issue, is there any way I can see the unique
names of all my check boxes again? Is there any mode which I have to use to
be able to see the unique names? What am I missing here?
Any help would be greatly appreciated. Thanks in advance. Tag: Can i link various text in a cell to a picture Tag: 981050
Userform Initialize Problem !
Dear Sir,
I have a Userform for the data entry purpose, when I press the OK
button then records are written on the sheet, and one message is
appearing YES/NO.
if I press Yes, then Userform must be clear all fields. but at the
same time message appearing "Please use numbers only"
infact I put the restriction on the Unit Price field, that should be
numbers only.
then I dont understand why this message is coming when I press OK
button.
any suggession ???
I AM USING THE BELOW CODE
=========================
Private Sub CommandButton3_Click()
' for ok button
ActiveWorkbook.Sheets("DailyPurchase").Activate
Range("A5").Select
' to fill up combo box for the category
If ComboCategory.ListIndex = -1 Then
MsgBox "You must choose the category SHAHZAD"
ComboCategory.SetFocus
Exit Sub
End If
' to fill up Date
If DatePurchase = "" Then
MsgBox "you must provide date"
DatePurchase.SetFocus
Exit Sub
End If
' to fill up QTY
If txtQuantity = "" Then
MsgBox " you must provide Quantity "
txtQuantity.SetFocus
Exit Sub
End If
' to fill up Unit price
If txtUnitPrice = "" Then
MsgBox " you must provide Unit Price "
txtUnitPrice.SetFocus
Exit Sub
End If
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = DatePurchase.Value
ActiveCell.Offset(0, 1) = TxtDescription.Value
ActiveCell.Offset(0, 2) = txtQuantity.Value
ActiveCell.Offset(0, 3) = txtUnitPrice.Value
ActiveCell.Offset(0, 5) = ComboCategory.Value
ActiveCell.Offset(0, 6) = txtPR.Value
ActiveCell.Offset(0, 7) = txtRemarks.Value
' Range("A5").Select
' Ask and Do Procedure
If MsgBox("One record is written, do you have more entries ?",
vbYesNo, "Title") = vbYes Then
Call UserForm_Initialize
Else
Unload Me
End If
End Sub
Private Sub UserForm_Initialize()
TxtDescription.Value = ""
DatePurchase.Value = ""
txtUnitPrice.Value = ""
txtQuantity.Value = ""
txtPR.Value = ""
ComboCategory.Value = ""
txtQuantity.Value = ""
txtRemarks.Value = ""
DatePurchase.SetFocus
End Sub
Private Sub txtUnitPrice_change()
If Not IsNumeric(txtUnitPrice.Text) Then
MsgBox "Please use numbers only"
Exit Sub
End If
End Sub
===================================
Please give me the solution, I dont need this message when I press OK
button.
Regards.
Shahzad Tag: Can i link various text in a cell to a picture Tag: 981049
worksheet SaveAs WK4 in background
I have an excel workbook with multiple sheets. I want to save one of
the sheets as a WK4 in the background. I amusing the following code:
Sheets("sheetx").SaveAs Filename:= "C:\xxx.wk4", FileFormat:=xlWK4,
CreateBackup:=False
The problem is that although the excel appears to still be open with
all of the tabs, it has been renamed at the top to the WK4 filename.
If I close the excel and open the WK4 then it opens as WK4 with only
the sheet that I saved. My problem is that users may have an issue
with the excel being "lost" while the supposed WK4 file looks like
excel.
SaveCopyAs does not seem to help because that cannot be done against a
single sheet and cannot convert to WK4.
Any ideas about how I could do a SaveAs only in the background without
renaming the excel? Tag: Can i link various text in a cell to a picture Tag: 981047
Please help!
I want to set up a condition where a row is deleted ONLY if BOTH cells equal
zero. Currently the macro below delete the row when the first cell = zero.
Thus when there is something in the second cell and the first cell is zero
the row is deleted.
The code is below if someone could assist I would be greatful.
'
Sheets("Report").Select
Cells.Select
Selection.Copy
Sheets.Add
Cells.Select
ActiveSheet.Paste
Range("D11:H120").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Dim DeleteFlag As Boolean
DeleteFlag = True
While DeleteFlag = True
DeleteFlag = False
Range("A11:H120").Select
For Each rw In Selection.Rows
If rw.Cells(1, 4).Value = 0 And rw.Cells(1, 9) = 0 And
IsNumeric(rw.Cells(1, 1).Value) And Not IsEmpty(rw.Cells(1, 1)) Then
rw.Delete
DeleteFlag = True
'rw.Cells(1, 10).Value = "Zero"
End If
Next rw
Wend
Application.CutCopyMode = False
Range("D16").Select
ActiveWindow.Zoom = 85
Range("A3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Name = Right(Trim(Selection.Value), 5)
Application.CutCopyMode = False
Range("A3").Select
Sheets("Macro1").Select
Range("C2").Select
Selection.Delete Shift:=xlUp
Sheets("Report").Select
Range("A3").Select
ActiveCell.FormulaR1C1 = "=Macro1!R[-1]C[2]"
Range("A3").Select
Sheets("Data").Select
Range("D4:E88").Select
Selection.ClearContents
End Sub Tag: Can i link various text in a cell to a picture Tag: 981043
Setting up Sum ranges inside a macro.
When I try to set up a sum function in macro, the vertical range of
the sum becomes an absolute range that does not expand when I try to
add rows between the first and last row of the range set in the macro.
The range does not expand. It seems to be anchored at the top of the
range and only extends the number of cells down that was set in the
macro. How do I set up a sum range that allows new rows to be added
inside the range and make the range expand to include the new row of
cells? Tag: Can i link various text in a cell to a picture Tag: 981040
Checkboxes with the same Group Name not Responding
I have 4 Checkboxes in a UserForm. I have given all 4 Checkboxes the
GroupName = "Shipping" in the Properties Window in the VBA Editor. We I Show
the UserForm and click on one Checkbox the others do not turn off, why?
Thanks in Advance,
Ryan Tag: Can i link various text in a cell to a picture Tag: 981033
Identify shape in cell
I have a sheet that was populated with data from a table copied from a
website. One of the rows has cells which contain shapes rather than
text. I need to convert these shapes to text and delete the shapes.
Two questions:
How can I take the current selection and determine just what shape is
in it?
How can I delete that shape after I set the value property to the
desired text?
Something like TypeName(Selection) will tell me that I have a shape if
what is selected is the shape itself. I actually want to select the
cell (or a range of cells) and, based on this, figure out what the
shape might be.
Thanks!
-Martin Tag: Can i link various text in a cell to a picture Tag: 981029
Iterate rows of spreadsheet(s}\Get values
I have two spreadsheets. The first one has a column with numbers in it.
The second one has a column with the same numbers and a description
column as well. Each number can have more than one description. What I
want to do do is iterate the rows in Sheet 1 and lookup the numbers in
sheet 2. Then return all the descriptions to sheet 1 and put them in
the cell next to the number. If there are multiple descriptions for a
number we would like them separated by a comma. Is there a function for
something like this?
TIA Tag: Can i link various text in a cell to a picture Tag: 981027
Help with match function, i need to tally
Ok, I have thousands of rows of data. One of the columns is titled "age" and
each row has a different age. Ranging from 15-100. I need to tally these
rows into age group, 0-18, 19-30, 30-50 etc etc.
Is there a code i can run that will tally these for me?
Thanks,
-Adam Tag: Can i link various text in a cell to a picture Tag: 981020
ADO help: Only get first 255 characters of field...
Hi,
can somebody help with this?
I have an Access database, that has a table with a memo field in it.
This table contains
records longer than 255 characters.
I am using ADO to get this info out of the database into a recordset,
and then loop through the
recordset to populate a spreadsheet.
However, I only get the first 255 characters for the data that comes
from the memo field.
How do I get the entire value stored in the database?
Somebody mentioned using "adLongVarWChar". But how do I do this?
Please help,
Thanks, Tag: Can i link various text in a cell to a picture Tag: 981019
distributing a spreadsheet with macro buttons
How do you distribute an Excel 2003 spreadsheet application with buttons
linked to macros?
Do you actually have to relink all buttons to the macros for every new
location? I can't believe that - I must be overlooking something.
My spreadsheet has buttons which are linked to macros.
When the spreadsheet is moved to a different location (on the same computer
or a new computer), the following error occurs when a button is clicked:
"A document with the name fubar.xls is already open. You cannot open
two documents with the same name etc."
I moved all macros into the Sheet1 Excel Object. That didn't help.
I have tried using buttons from the control toolbox (which I read should
correct this but doesn't), and from the forms toolbox, and the "custom menu
item" from the macros list. Nothing works.
When I put the spreadsheet on another machine, sometimes the buttons are
there but I have to link them to the macros. Sometimes the buttons aren't
there at all.
I don't understand this and can't find anything to clarify it. Where are
the toolbars stored? Why can't the buttons just look in the current
workbook for the macros? Tag: Can i link various text in a cell to a picture Tag: 981004
Easy Worksheet Function help
Where did i go wrong?
=IF(F4='> 100',"1",)OR(F4='76 - 100',"2",)OR(F4='51 - 75',"3",)OR(F4='26 -
50',"4",)OR(F4='< 25',"5")
Basicallyâ?¦
If F4 is equal â??> 100â?? then display a 1 or, if F4 is equal to â??76 â?? 100â??
then display a 2 or, if F4 is equal to â??51 â?? 75â?? then display a 3 Tag: Can i link various text in a cell to a picture Tag: 981003
UDF Function to calculate mean moving range (control charts)
I was about to post a question as I was trying to create a function
that would calculate the moving mean range which I would then be able
to use to calculate the UCL and LCL for a series of data i.e
Mean + 2.66*Moving mean range, without having to calculate all of the
variances between each 2 sets of data....hope that makes sense.
However, I have cracked it and wanted to post the Function in case
anyone else wants it:
It's pretty simple stuff as it is the first user defined function I've
written so apologies if this is clumsy code....
Function MMR(CalledCells As range) As Variant
Application.Volatile True
Dim V1 As Variant
Dim V2 As Variant
Dim V3 As Variant
Dim i As Integer
Dim FirstRow As Long
Dim LastRow As Long
V1 = 0
FirstRow = CalledCells.Row
LastRow = CalledCells.Row + CalledCells.Rows.Count - 1
For i = LastRow To FirstRow + 1 Step -1
V2 = Abs(Cells(i, CalledCells.Column).Value - Cells(i,
CalledCells.Column).Offset(-1, 0).Value)
V3 = V2 + V1
V1 = V3
Next i
MMR = V1 / (CalledCells.Rows.Count - 1)
End Function Tag: Can i link various text in a cell to a picture Tag: 980999
Trying to create Macro to delete duplicate Rows
Hello,
I am hoping someone can help me. I am not an experienced macro writer and I
am having a problem on how to write a macro on deleteing duplicate rows. To
give you an example of what i am trying to do is:
I am trying to delete rows with duplicate information that is in column
A,S,T,U,V,W, Y, and Z.
But i want to leave at least one row with that info.
For example if row 1, 2, and 3 have the same info in A, S, T, U, V, W, Y,
and Z. I would want to leave row 1 and delete row 2 and 3.
I am hoping someone can help.
Thanks Tag: Can i link various text in a cell to a picture Tag: 980988
Optimising Code
I have the following that is part of a larger piece of code that formats a
worksheet. Currently, this part takes 15 minutes to run and I would like to
know if I could have written this code in a better way:
For Each c In r
If c.Value = "Complete" Or c.Value = "Not Applicable" Then
With Cells(c.Row, c.Column - 28)
.ClearFormats
.Interior.ColorIndex = 5
.Font.ColorIndex = 2
.Font.Name = "Verdana"
.Font.Size = 8
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.NumberFormat = "dd/mm/yy"
End With
End If
Next
This is running on a area that is 203 rows x 17 columns, the number of rows
varies.
I cannot use conditional formatting as it already exists in the target cells
and has to be removed and reformatted as above. Unless there is a better way,
of course!
Many thanks for any suggestions
Louise Tag: Can i link various text in a cell to a picture Tag: 980987
I would like to link text in a cell to a picture so when i select or write
text in a cell different pictures will appear next to the cell the text is in.
Re: Can i link various text in a cell to a picture by Ken
Ken
Fri May 09 05:05:02 CDT 2008
On May 9, 4:05 pm, Tim Hornby <Tim Hor...@discussions.microsoft.com>
wrote:
> I would like to link text in a cell to a picture so when i select or write
> text in a cell different pictures will appear next to the cell the text is in.