Excel 2007 :: User Friendly Clickable Button?
Sep 5, 2013
I have a file that contains detailed sales information for each sales person. It typically contains over 20k records and the managers find it difficult to look up their sales people.
Currently it is subtotaled for each sales person but there are about 450 sales people across the country and it's not easy to use.
I have seen other Excel files that use buttons and I am wondering if this might be the solution to this issue. In other words, can I create a button where the managers can just click it, key in their sales person's name and get at the records easier? If so, how does that work? How I could make this file more user friendly.
View 9 Replies
ADVERTISEMENT
Nov 14, 2011
I am creating a userform in Excel 2007 which requires a user to pick their name from a drop down box then press Ok, what i want to do is disable the Ok button until the user field has been selected.
View 1 Replies
View Related
Jul 14, 2009
I am writing a macro in which the user selects several ranges (entire columns) for the macro to manipulate. I display a userform to the user that contains 16 refedit objects (1 for each range that they picked). Unfortunately, the value that appears in the refedit boxes is not very user-readable. It looks like this:
'[WorkBookName.XLS]Sheet1'!$B$5
I can assume that all of the ranges will reside in the same workbook, so I have been cutting the workbook name portion off as a string and displaying it in 1 place only. I can leave the value of the refedit as: Sheet1'!$B:$B, but even that is not very friendly. Of course if I do change the value of the refedit controls then I will need to "fix" them before attempting to access the range.
Does anyone have a suggestion as to how to display the sheet and column of the range to the user?
View 6 Replies
View Related
Jun 5, 2009
attached is a sample schedule, there is conditional formatting in all of the cells (see graphic) such that if the cells to the left are the same value, then the cell is turned white, and a border is removed.
This gives the appearance of a 'block of time' while which cleans up the appearance of the spreadsheet.
the problem i have is that when we scroll to the RIGHT, we lose track of the 'left most' cell...which is black of course, the similar ones to the right are intentionally whitened out.
a perceived solution would be to change the conditional formatting of the column (actually only the cells in the column we care about) when it is NEXT to the vertical freeze pane so that it appears not in white, but in black font, etc.
the TRICKY part is that we will obviously need to go back and forth, so the columns will need to change formatting often.
or
when the column number (or letter) next to the freeze pane changes, change the formatting to certain cells in that column to an alternate version; and change the formatting of all other certain cells within other columns to original formatting[
View 9 Replies
View Related
Mar 8, 2008
I ‘m trying to put a drop down combo box on each sheet (around the top right) of a workbook (20+ sheets). The drop down list will have each sheet in the workbook. Selecting the sheet name in the list will make that sheet the active sheet. I plan to remove the tabs from the window, because only about a third of the tabs display at once, and right clicking to the right of the tabs only displays about 2/3 of the sheets.
When I was searching this topic, I found a post that used AllWorkSheets function to create an array of worksheet names and the procedure adds it to the data validation in cell "A1". I don’t have the same cell available on all the sheets, at least not at the top of the page, so I thought a combo box that can be moved would be a good solution, but I’m open to any suggestions that allows the user to navigate the sheets w/o a macro always running or a form always open.
View 2 Replies
View Related
Nov 21, 2009
Want to go to a particular sheet in the same workbook by selecting the sheet name from a list in sheet 2 and clicking a command button. Sheet 2 has a list of all sheets in the workbook and sheet names are in two cells in column A & B (e.g. tdm-216). User to select two cells which are the desired sheet name (all sheet names are unique) and then click macro to jump to that sheet in the workbook. There will be over 100 sheets in the workbook.
View 6 Replies
View Related
Apr 15, 2009
I am trying to create a password on a clickable button in Excel. I have put the following code in:
View 11 Replies
View Related
Oct 14, 2008
I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.
View 2 Replies
View Related
May 7, 2014
I have created a macro that a couple of us can run at the end of the day that looks in a preset folder and has whatever .xlsx files in that append to each other creating one .txt file. It then saves that .txt file and runs a batch file that strips off the .txt file extension. This is the format we need the ending file to be in so we can FTP it to another agency.
Myself and another coworker can run it without any problem and it asks us if we want to save the .txt file before it closes it and runs the batch file stripping off the .txt extension. We say yes and it executes as designed. Another coworker runs it and it looks like the files are appending then the window closes. Never prompts him if he wants to save and the file is nowhere to be found. From what I have researched all his excel settings match ours. We are all running the same OS (XP) and version of MS Office (2007). The part of the macro that seems to just be ignored is below:
[Code] .......
Then the following batch file runs stripping the .txt file extension:
FOR /R "T:Cash ManagementUnsecured\_Team - DisbursAUTOMATIONInput" %%f IN (*.txt) DO REN "%%f" *.
I have tired removing the ActiveWorkbook.Save command thinking it would default to prompt him to save it but it doesn't.
View 2 Replies
View Related
May 24, 2012
In the spreadsheet shown below I would like the user to select a project via a dropdown list in cell B2 which is from the data set shown in row 7 downwards. Then based on the project they select, they need to be able to see all of the locations associated with that project and choose in B3. Finally they then need to be able to select a team which is associated with the project & location combination chosen in cells B2 and B3.
Excel 2007
ABC2
Project3Location4Team56ProjectLocationTeam7Project 1PerthTeam 18Project 1MelbourneTeam 29
Project 1SydneyTeam 110Project 1Brisbane Team 111Project 1Brisbane Team 312Project 1DarwinTeam 413
Project 1DarwinTeam 514Project 2PerthTeam 315Project 2PerthTeam 416Project 2MelbourneTeam 117
Project 2MelbourneTeam 218Project 2SydneyTeam 419Project 2Brisbane Team 6
View 9 Replies
View Related
May 20, 2014
I ma using exel 2007 and I tryed to use this codes,I need faster to finish this project
Soo I have done only 2 buttons OK and Close the tab works like this I CLICK "Kerko" and then I write the name that I want to search in my Phonelist and when I click Ok it shows me his surname his telephone name and his City prephix
But the next and previous button I cant make them work,soo when I have to click next the name down the name that I first wroted will apear with his surname telepphone name and city prphix,and when I click prevoious will happend the same think but will not apear the next persone but the previous.
View 1 Replies
View Related
Nov 26, 2011
I want to import data from 1 excel sheet to another sheet in excel 2007 by clicking the button which i was created through.
Developer>insert>forum control>button
View 9 Replies
View Related
Jan 10, 2012
I'm trying to work with a user form with a customized SUM button.
What I want is that the user can select a range and then press SUM and it will sum (and store) the range and then the user can click another button to place the sum in any other cell.
Here is the code I have on the SUM button but it fails
Code:
Private Sub CommandButton1_Click()
MsgBox (Application.Sum(ActiveWorkbook.ActiveSheet.Selected))
End Sub
I'm using a userform1.show vbmodeless so the user can click off the form. I tested it being able to work cross workbooks and sheets and I could see where it was returning the activeworkbook/worksheet but I don't understand the range/selected part.
Excel 2007
View 6 Replies
View Related
Sep 26, 2013
My excel version is 2007 and i am on WIN7 64bit
I have a workbook with VBA which was working fine earlier. Lately i have been getting
"Runtime error 1004" Select method of button class failed.
I am adding buttons dynamically in a loop in a sheet and what surprise me is , i go to debug mode when error pops up and it points to below line
MySheet.Buttons.Add(ActiveCell.Left - 5, 25, 20).Select
but my code is running in a loop and i can see that atleast 20 buttons were added and it fails to select when it try to add this iteration. It is completely confusing me.
My entire below code is in a loop
maWrk.Cells(xIt1, yIt2).Select
mySheet.Buttons.Add(ActiveCell.Left - 5, 25, 20).Select
Selection.OnAction = "callMe"
[Code].....
I also notice that when it fails, on select method .. the button was actually added to the sheet with caption/name as "Button 65536" but then fails to select it. What makes the select to fail after adding the button? Could there be anything special with Button 65536?
Is there any better way to code the adding button and setting action and name for excel 2007?
note before entering the loop i am deleting all the shapes with myShape.Delete which name matches "btRun"
View 3 Replies
View Related
Apr 19, 2014
I have managed to create a command button in excel 2007 that performs a macro upon a click. But, after re-opening the workbook, I am unable to right click on the button to recall the properties window.
View 2 Replies
View Related
Aug 2, 2013
We just upgraded to Excel 2007 from Excel 2000 and have run into a challenge relating to labeling a form command button. It appears that the length of text for a command button label has been shortened to 31 characters when setting the name using VBA. No such limitation shows up when I manually create such a button.
Following is the code that used to work to create the button and label in VBA (Excel 2000):
VB:
ActiveSheet.Buttons.Add(2.25, 13.5, 443.25, 17.25).Select
Selection.OnAction = "PatientSelectedButton"
Selection.Name = "CheckFormButton"
[Code] .....
If I change the string I want to use for the label to 32 characters, or less, this code works (Excel 2007).
VB:
ActiveSheet.Buttons.Add(2.25, 13.5, 443.25, 17.25).Select
Selection.OnAction = "PatientSelectedButton"
Selection.Name = "CheckFormButton"
[Code] ....
I have to admit that this is the last item that I thought would break!
View 4 Replies
View Related
Aug 9, 2013
I've been trying to research how to do this for a while now and still can't figure it out. My impression is that this is fairly straightforward in Excel 2010 since the menus and ribbons are easily customizable but not so in Excel 2007.
So far I can get macro shown as a button in the Quick Access Toolbar. But the issue is that I can't organize them or customize the icons.
Is there an accepted practice for this?
View 4 Replies
View Related
Oct 25, 2012
I am using the following piece of VBA code in Excel 2007 to automatically generate an email when a button is pushed:
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double
r = ActiveCell.Row
'Get the email address
Email = Cells(r, 3)
[Code] .....
The button to launch this code sits in cell AK7. What I would like to do is have the macro copy the email address present in cell E7 into the email address section of my created email. In addition I would like the reference provided in cell AJ7 to appear in the Message Subject part of the generated email in the format " RCS Reference contents of cell AJ7".
View 3 Replies
View Related
Dec 17, 2012
Excel 2007 I have a button that will export the worksheet to pdf and save it as Acrobat requires. I have a network folder set up and it saves the file with the name that I have programed in the macro. I would like to have the file name set up to be what is in cell C3 then a space and the specific words.
For example, if C3 contains "123456" I want the file to be named "123456_Warranty Calculator"
I will end up using this in several worksheets which will have a different name as part of the file name (based on the worksheet name).
I would also like to have the last part of the file name be the worksheet name. ie, "Warranty Refund", "PDR Refune", etc.
I will have several users that will be using the workbook and possible saving at the same time and want each person to be able to find the one they saved instead of it being overwritten.
Code:
Excel 2007: I have a button that will export the worksheet to pdf and save it as Acrobat requires. I have a network folder set up and it saves the file with the name that I have programed in the macro. I would like to have the file name set up to be what is in cell C3 then a space and the specific words.
For example, if C3 contains "123456" I want the file to be named "123456_Warranty Calculator"
I will end up using this in several worksheets which will have a different name as part of the file name (based on the worksheet name).
I would also like to have the last part of the file name be the worksheet name. ie, "Warranty Refund", "PDR Refune", etc.
I will have several users that will be using the workbook and possible saving at the same time and want each person to be able to find the one they saved instead of it being overwritten.
View 2 Replies
View Related
Jun 20, 2013
I already have a Macro button built that hides values in column A. The next step I am trying to perform is a custom sort on column B in this order "Backordered", "Sourced", Shipped", and "Received". Here is my code that I have so far but where to begin adding in code to make this button sort.
Sub Inbound()
ActiveSheet.Cells.EntireColumn.Hidden = False
ActiveSheet.Cells.EntireRow.Hidden = False
Application.ScreenUpdating = False
[Code] ........
Excel 2007/Windows 7
View 1 Replies
View Related
Jul 28, 2014
I'm trying to change a cell in a 2007 workbook but I'm getting this message:
"the cell or chart that you are trying to change is protected and therefore read only. To modify a protected cell or chart first remove the protection using the unprotect sheet command (review tab, changes group) you may be prompted for a password."
However, the unprotect button on the changes group tab is greyed out (the protect button is available though). The workbook isn't password protected and I'm not being asked for a password. What am I missing?
View 2 Replies
View Related
Feb 6, 2014
I have a workbook with two worksheets, "Main" and "Control".
Both of them are with hidden tabs (unchecked "show sheet tabs").
On worksheet "Main" I have command button which opens worksheet "Control".
The assigned makro is: Sheets("Control").Select
The problem is: when I open "Control" and close after that the workbook, the next time when I open the workbook "Control" pops-up instead of "Main" even though I do not save it.
1. I need macro on "Main" which will allow me to open "Control".
2. Regardless I "Save" or "Don't Save" "Control" when selected and workbook is closed, to open the workbook always displaying worksheet "Main".
I use Excel 2007 (at home) & 2010 (at office).
View 1 Replies
View Related
Sep 26, 2013
My excel version is 2007 and i am on WIN7 64bit
I have a workbook with VBA which was working fine earlier. Lately i have been getting
"Runtime error 1004" Select method of button class failed.
I am adding buttons dynamically in a loop in a sheet and what surprise me is , i go to debug mode when error pops up and it points to below line
MySheet.Buttons.Add(ActiveCell.Left - 5, 25, 20).Select
but my code is running in a loop and i can see that atleast 20 buttons were added and it fails to select when it try to add this iteration. It is completely confusing me.
My entire below code is in a loop
Code:
maWrk.Cells(xIt1, yIt2).Select
mySheet.Buttons.Add(ActiveCell.Left - 5, 25, 20).Select
Selection.OnAction = "callMe"
[Code]....
I also notice that when it fails, on select method .. the button was actually added to the sheet with caption/name as "Button 65536" but then fails to select it. What makes the select to fail after adding the button? Could there be anything special with Button 65536?
Is there any better way to code the adding button and setting action and name for excel 2007?
note before entering the loop i am deleting all the shapes with myShape.Delete which name matches "btRun"
I also want to highlight that i don't have 65536 rows in my sheet. I just have 200 rows. and when this error happens it is on row 150 so firstly i wonder why it is naming the button as Button 65536. I would assume it will start with 1 and go on increments.
What makes excel to fail to select an added button in VBA?
View 6 Replies
View Related
Jul 11, 2012
I have an excel file with multiple sheets. I want my HR team to update that file, but one user shouldnt see the entries of other users. I want to make a consolidated file out of three files. This excel template is for salary input.
View 3 Replies
View Related
Jul 22, 2013
An Excel file named CLEARING is availble in thr central server system. it will be accessed by 5 or 6 staffs from some other systems what i need is when one person accesses/edits this file the others should not even able to open in the read only file ,when the file is saved and closed ,then only the next person should access it
View 1 Replies
View Related
Jan 23, 2009
I would like to set up an Excel spreadsheet for conducting a time and motion study in a workplace setting where someone follows an employee around to document how much time is spent on a variety of activities. Since the person would have a mobile tablet PC to walk around with and track the data, I would like to capture 3 fields of information:
1) Category of function being performed
2) Start date/time of function
3) Stop date/time of function
For #1, I would like to have a drop down box that appears in each row of Column A with a predefined list (i.e. Activity A, Activity B, Activity C, etc.). For #2 and #3, I would like to create a macro that "timestamps" the current date/time when a blank cell is "clicked".
View 5 Replies
View Related
May 31, 2009
I have joined here with a great expectation. I used the Forum MR Excel , i posted my question there but no one replied . I hope i will get the answer to my question here. My question is
I Have Combo box
When i click on it i get three Things which i have inputed in the list .
1 ) Machine
2 ) Car
3 ) Bike
With each list , a seperate sheet is assign with complete details like machine for machine sheet , car for car sheet and bike for bike sheet .
Now what i want is when i click on the machine i want the Machine sheet to popup within the same workbook , and when i click car i want the car sheet to popup and same for Bike .
I tried to Assign Macro , but for each list , i am getting the same / last result
here is the code
View 14 Replies
View Related
Jan 22, 2008
I have a userform which takes a large amount of sequential geographical data and builds a theoretical model of my layout whithin excel. However I would now like to incorperate an interactive aspect to this userform. I have written code which draws a line diagram whithin a seperate CAD app and imports it as a picture to the excel userform each time the user updates the data, but I would like to be able to select different areas whithin the data by clicking in the appropriate place on the picture in the form. I know this kind of interactivity is a bit beyond visual basics line of duty but my model MUST stay whithin the confines of excel thereforwe my front end must remain VBA for the time being.
My other option is to build up a diagram out of smaller components (boxes etc) whithin the userform. This would be entirely acceptable, however I'm not too sure if its possible to 'create' new pictures on the form from whithin the code.
View 9 Replies
View Related
Sep 4, 2009
I wish to create a button which will allow the user to goto the page in my spreadsheet.
I no i can use macros or hyperlinks to do but i can only use this button once for it to work. I want to cut and paste the button to save time.
View 10 Replies
View Related
Jul 21, 2014
Within a worksheet, I have several radio buttons, allowing the user to select only one of them. Using VBA, how do I recognize which radio button the user selected?
View 1 Replies
View Related
