I am trying to find some better looking command buttons for userforms in excel VB.
Maybe like the ones you can use in Access from the command button wizard. inoculars for find, door for exit etc. Does anyone know how to get these in excel VB editor?
I am using a spreadsheet as a score sheet for a competition. One of the columns is the student's GPA. After entering all the scores there are duplicate final scores. I need a way to have it look at the final score and then use the GPA so that it will not put a duplicate value in the final column.
al Column N is the Total Column, Column O has the Names that correspond to the Total Column. Currently I am taking this total and putting it into Column Q (High Scores) in high to low order. Column R should have the names that match the scores. But with duplicate scores, it is only putting the first name associated with the score. I would like to use the GPA as a final determining factor for the duplicate scores. The higher GPA would come before a lower GPA. I have tried to put an additional column to bring the GPA over to correspond with the High Scores Column, but could not get it to work.There are actually more names for the competition and the top 10 will be moved to a different sheet and further judged. I have attached a sample with the exact formulas that I am using.
im currently trying to link a commandbutton1 (calckatz) on one sheet to activate another commandbutton2 (megkatz) on another sheet.
the code for commandbutton2 is
Private Sub MEGkatz_Click()
'Meg calcs KATZ
Range("F6").Select
Range("F6").GoalSeek Goal:=Range("F5"), ChangingCell:=Range("D9")
end sub
I have a file which contains many worksheets with many buttons on them for doing administrative work when updating data. I want to be able to click on a button on my "admin" worksheet to hide all the other buttons on the other worksheets. This is hide the buttons from the view of 'readers' so that they will not be tempted to click on a button to see what it might do. I have tried several different variation of coding, but can not seem to get the "right" object to work with so as to set the code to be xxxx.visible=false. I seen references to Shapes, Shape, Control Toolbox buttons objects, CommandButton, etc.
I would really appreciate if someone could give some pointers on how to designate in VB code, which will lend itself of future updates, the following:
in a toogle button, loop through the series of worksheets making each command button on that sheet set visible = false. Then for the other portion of the toogle repeat the process, but set visible = true.
I want to create an event handler for multiple buttons. I know that it can be done in VB but I'm not sure about VBA. Auto Merged Post;bump* Auto Merged Post;bump*
View 4 Replies View RelatedI am making an extended spreadsheet schedule for a friend, almost done except how to do this easy: There are 135 (!) cells that will represent day and time in a workweek if someone is available or not. Easiest procedures we could think of is make the cells some how change color, all red by default (unavailable), change them to green if someone is available. We were making a button change its color, but it will require a lot of programming (index numbers to remember) is there an easy way to program 135 buttons or cells to change its color on a mouse click?
View 7 Replies View RelatedI have frame on a userform with command buttons. Is it possible to loop thru and capture the command button captions in a textbox. I'm trying to put together keyboard on a userform, so the user can populate a textbox with letters or phrases.
View 6 Replies View RelatedI am working on a project that requires Other or Additional info to be available if I click on a specific item. For example, if I click on a person's name a message or pop up show appear with the person's address or any other information I might want to add. Is this possible in Excel or should I be using a different program?
View 1 Replies View RelatedI have a macro attached to a command button to clear entries in my workbook for me however, I left out one tab that needs to be cleared.
I need to add the following tab and cells.
Add Tab "Time Off Glance", Cells (B1:P35) to the same command button.
The code I have now is:
Private Sub CommandButton2_Click()
Dim rng As Range
Dim x As Integer
Dim y As String
For x = 1 To 27
y = x
Union(Sheets(y).Range("B9:O15"), Sheets(y).Range("Q9:T15")).ClearContents
Next x
End Sub
I have a code that searches in a column for a certain value, if it finds it, it copies this value to a range adjacent to it.
[Code] ..........
However, if the cell where the code is supposed to find a new value is blank it makes the range blank. But in that case, I wan't it to leave the range cells with their original value.
I am building a database of music files and using Excel(2007) to catalog them. As an extra bonus I'm setting up a sheet of statistics about the database with suchs things like the # of tracks and longest song, etc.
Most of it has been easy to figure out but there's one formula I'm not quite sure how to do, or the way I think it is to be done is incorrect or contains some error on my part. I already have a cell that gives me the length of the longest song in the database. What I want it to do is give me the name of the longest song. This is what I tried to do, without success, giving me an popup error telling me that the formula has an error.
I have one sheet that receive data automaticly.
I have colorfunction in the end to count the red,yellow and green points.
My problem is sometimes there are points that have more than one
coordinate (X,Y,Z) as you can see on line 6,7,8 and they should count only as one.
The criteria for that is if any of them is yellow it should count as yellow,or if any is red it should count as red, always the worst result.
So we created a workbook that has lots of worksheets inside it. The problem is due to the high number of worksheets there are alot of tabs along the bottom. Many of the tabs are hidden behind the scroll bar. Is it possible to have 2 rows of tabs at the bottom of the screen?
View 2 Replies View RelatedI'm working on a spreadsheet that needs a lot of check-boxes and every time I add one I get the text "Check box" right next to it. I can't seem to get rid of it.
Format control - Alt Text didn't work.
I have problem with additional "hidden"(duplicated?) sheets (not by atribute)
This is my excel, created from new fresh file : excel tables.bmp
And this is what i see when i get all sheetnames (with C#, word and some other programs) word-tables.JPG
where A1$ have same value as A1$_4349_inkjet_WZ
I have this problem with some other files but in this one i have 100% confidence that none of sheets is hidden nor very hidden.
I found that 3 proper tables are "System table" and 2 additional are "tables".
[URL] .....
I have an existing macro that convert active sheet to pdf then email it as an attachment thru outlook.
now, what i need is to attach another pdf file in it, so it will now be 2 attachment. here is my codes:
var all_spans = document.getElementsByTagName( 'SPAN' );
for( var i =0,skip =0 ; i [code]......
I have seen this on many spreadsheet but never figured out how to do this - click on a cell in a spreadsheet to open up a new spreadsheet?
View 4 Replies View RelatedI need to add a condition to a statement that adds a "If TRUE" reference to another cell.
The other cell can be either a TEXT value "RM1" OR "TE2". If neither condition is true than the value is blank or zero.
So right now I have in the destination cell =IF(AB7="CONT",V7,"") That works lovely, but really isn't countable unless cell "Z7" ALSO has Either "RM1" OR "TE2".
If cell "Z7" has the text "DEA" or "SP" then the value of "V7" will be placed in a different column. I will use a different destination column (for this modified "DEA" or "SP" condition)
I have a spreadsheet that records data on various sites with data entered on a userform.
Every so often a site may need to be added or deleted. Is there anyway to use VBA to add an additional field to a Userform, and then also delete the field if necessary?
Trying to perform a match of 2 sets of data.
Data set 1 is 2 columns: A = ID number, B = Text entry
Data set 2 is 1 column: C = Text entry
All of the cells in column C can be found in column B so I'm trying to match that data but I also want to create a new column that lists the ID number for the matched cell.
For Example:
Column A
Column B
Column C
Desired Result
1
Sock
Underwear
9
[code].....
I've tried to do this MATCH and VLOOKUP. Kept getting error messages. I threw a stapler.
The codes below allows me to change the color of the cells in Col F & G dependence on the value of Col G. I like to add another condition and that is if Col K the value is cancelled or closed the whole row will be CellColor = 48: FontColor = 2
Sub Risk_Color()
Dim c As Range, myFontCol As Integer, myCol As Integer
For Each c In ActiveSheet.Range("f7:g20000")
myFontCol = xlAutomatic
myCol = xlNone
Select Case c.Value
Case Is = 1, 2, 3
myCol = 34....................................
when the user enters the letter Y in a cell within a set column a macro kicks in and enters a VLOOKUP formula in a cell 1 row above and 4 columns to the left by using ActiveCell.Offset (-1,-4).
This seems to work fine however when I try and get the coding to copy and paste special as values it just seems to ignore it?
i have a macro that copies info from cell in row A and uses that info to name the whole row, now if the name has a space between the words it uses _ (underscore) substition so i don't get an error (when row name is defined - you can't use any special characters or spaces) i was wondering if anyone can help me to add "-" besides the space
here is the macro
Sub Macro1()
Dim a As Long
For a = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(a, 100) = Application.WorksheetFunction.Substitute(Cells(a, 1), " ", "_")
ActiveWorkbook.Names.Add Name:=Cells(a, 100), RefersToR1C1:="=Sheet1!R" & a
Next a
End Sub
I've managed to create a drop-down list with the following possibilities:
Yes
No
Others
When the option Others is selected, I would like the user to be able to input additional information in a new line with wrap text formt (this new line should be hidden when either Yes or No is selected).
As a follow-up to Cannot Access Additional Controls From Toolbox.
I have been having the same problems adding additional controls to the toolbox in Excel VBA on Windows Vista running Office 2007 Pro.
I ensured I had a full install of Office and I was logged in with admin rights.
Two work-arounds:Create a form in Excel on a machine that has the control you need to use, then export the form. Copy it to the PC that doesn't have the additional controls functioning properlyOn my machine, creating a new profile and running VBA enabled the additional controls *only for this profile*, not the existing profile.
While these work-arounds don't solve the problem, they point to it being a profile issue, not an Office installation issue.
Hope this saves you the hours of Googling that I have spent trying to find a solution.
The code is as follows:
Sub addtaskpc()
ActiveSheet.unprotect
Application.DisplayAlerts = False
myrow = Cells. Find(" Total P&C Estimate").Row - 3
mycell = Cells(myrow, 2)
mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
.EntireRow.Copy
.EntireRow.Insert Shift:=xlDown
End With
Application.CutCopyMode = False
Cells(myrow + 3, 2) = "Task#" & mynum
Application.DisplayAlerts = True
ActiveSheet.protect
End Sub
I want to use excel to create quotations for customers. (currently doing it in word)
Tab 1 is the quote, has 4 columns, part number, description, cost, yearly maintenance
Tab 2 has all the things we sell, part number, description, cost, yearly maintenance. 150 lines.
So, Tab 1, description column is a drop down box from Tab 2. Working fine.
Question. When an item is selected from the drop down, how do I get it to then bring from tab 2 the part number, cost and yearly maintenance.
Sheet 2 in the attached sample workbook (without all the pre-existing formula's from the original) contains data that I paste in (Cols A to D).
Sheet 1 then searches that data (minus Col D) from Sheet 2 and returns "Yes" to Col I if matching data is found.
What I would like to do now is if Sheet 1 returns "Yes" then the corresponding value from Sheet 2 would be returned to Sheet 1 Col J.
So in this example the value "5" would be returned to Sheet 1 Cell J1
I already have all the formulas in place to find the matches and return "Yes", this is just to return the additional information from Col D Sheet 2 to Sheet 1 Col J..
I have two worksheets, 'monthly budget' and 'summary sheet'.
Summary sheet has been set up so that any additional records added to 'Monthly Budget' will be copied using the formula below (kindly provided by Cheeky Charlie yesterday):-
=IF(ISBLANK(INDEX('Monthly Budget'!$A:$G,ROWS($1:1),COLUMNS($A:A))),"",INDEX('Monthly Budget'!$A:$G,ROWS($1:1),COLUMNS($A:A)))
I have set up a unique entry count on 'summary sheet' which is currently showing 5. If I add a new name 'Jim' to the 'Monthly budget' sheet, this is copied on the 'summary sheet' however the unique entry count remains at 5.
I'm attempting to create a worksheet to consolidate his financial data.
How to add the last loop or where to place it.
I can copy the values from the P&L sheet to the DATA sheet but I cannot discover a way to also pull the values from the BalSht sheet to the Data sheet.
I think the BalSht loop will be almost identical to the "P&L sheet loop, and be placed just below it - but I'm not successful in multiple attempts and days of trying.
How can I accomplish this? Here's the VBA code:
[Code] .....