Macro To Button To Zip Files Dependent On Button Name

Jan 29, 2014

Context: I have a spreadsheet that contains a list of employee names and their certifications. I want to be able to assign a button to each employee in column B with a macro that is able to zip files from a folder that contains that employees name.

The following code assigns buttons to each employee in column B. At the moment the code I have is able to assign the macro "Zip" to each button.

VB:

[COLOR=#333333]Dim Btn As Button[/COLOR]
[COLOR=#333333]Dim rng As Range[/COLOR]
[COLOR=#333333]For I = 2 To RowCount + 1[/COLOR]
[COLOR=#333333]With Worksheets("Sheet1")[/COLOR]
[COLOR=#333333]Set rng = .Range("B" & I)[/COLOR]
[COLOR=#333333]Set Btn = .Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)[/COLOR]

[Code]....

The following code is my Zip macro:

VB:

[COLOR=#333333]Sub Zip()[/COLOR]
[COLOR=#333333]Dim strDate As String, SavePath As String, sFName As String[/COLOR]
[COLOR=#333333]Dim oApp As Object, iCtr As Long, I As Integer[/COLOR]
[COLOR=#333333]Dim vArr, FileNameZip[/COLOR]
[COLOR=#333333]Dim FName() As Variant[/COLOR]

[Code]...

Now, this Zip macro is able to zip the file for the employee "Anthony Tran". However I need it to be able to recognise which employee's button I've clicked and search the same files as above except with that employees name instead of "Anthony Tran".

If it makes things easier, the code for creating buttons for each employee is able to name that button as the employees name that it represents.

View 2 Replies


ADVERTISEMENT

Assigning Macro To Each Button To Zip Files Dependent On Button Name?

Jan 28, 2014

Context: I have a spreadsheet that contains a list of employee names and their certifications. I want to be able to assign a button to each employee in column B with a macro that is able to zip files from a folder that contains that employees name.

The following code assigns buttons to each employee in column B. At the moment the code I have is able to assign the macro "Zip" to each button.

[Code] .....

Now, this Zip macro is able to zip the file for the employee "Anthony Tran". However I need it to be able to recognise which employee's button I've clicked and search the same files as above except with that employees name instead of "Anthony Tran".

If it makes things easier, the code for creating buttons for each employee is able to name that button as the employees name that it represents.

View 6 Replies View Related

Macro Works With Form Button But Not Command Button

Oct 5, 2006

This is probably really straight forward but cant see why it happens, the following macro works fine when called by a button created by the form toolbar but doesnt when called by a command button, get the runtime error 1004, "select method of range class failed"

ActiveSheet.Select
Range("B4:B37").Select
Selection.ClearContents
Range("G4:G37").Select
Selection.ClearContents
Range("B1").Select
Selection.ClearContents
Range("D1").Select
Selection.ClearContents
Range("F1").Select
Selection.ClearContents
Range("J1").Select
Selection.ClearContents
Range("M2:M3").Select
Selection.ClearContents
Range("B4").Select
ActiveWorkbook.Save
Application.Quit

View 2 Replies View Related

Dependent Combobox Userform - Submit Button Isn't Working?

Jun 4, 2014

I am currently running VBA when ComboBox1 is selected ComboBox2 shows specific information based on lists in the spreadsheet.

Now I would like it if ComboBox2 showed "Business Improvement" then ComboBox3 will show information set in that list.

Also, my submit button isn't working and I can't find out what's wrong using the debugger

View 7 Replies View Related

Code For Button That Runs Macros Dependent Upon Which Boxes Are Checked?

May 16, 2014

See attached file illustrating exactly what I'm trying to do.

I won't repeat the exact same things I wrote in the file, but the Summary is that I need to first make such that only certain check boxes can checked at any one time, then make the command button run certain macro(s) depending on which boxes are checked.

View 8 Replies View Related

Open Different Files With 1 Button

Nov 3, 2008

I would like to create a button that will open a file in a location based on the date of the file. So if I click the button the file with the latest date is activated as read only. The file name is always the same except, the date is added to the file name each day.

View 14 Replies View Related

Opening Files Only On 3rd Command Button?

Jan 28, 2012

I have the following codes for 2 command buttons for a userform.However, the following codes opens the excel files that I've selected.The results that I want is to have a third command button such that when it is clicked then it will open the 2 files specified on the 2 textbox.How do i modify the codes?

Code:
Private Sub CommandButton1_Click()

Dim f As FileDialog
Set f = Application.FileDialog(msoFileDialogFilePicker)[code]......

View 5 Replies View Related

Toggle Button Pressed - Change Color / Highlight Button?

Nov 11, 2011

I have a togglebutton for an excel sheet in use for scoring psychological tests. When pressed, the button higlights the cells where a raw score is inserted.

What I need is a code to higlight the togglebutton when pressed, eg. green (colorindex 4). The button is white. How to do this?

Below is the code, It has more lines than needed, but it works.

Private Sub ToggleButton1_Click()
'FKV TOGGLE'

ActiveSheet.Unprotect "manisk"
If ToggleButton1 Then

[Code] ........

View 2 Replies View Related

Reference Cell Based On Button Location & Determine Button Used

Mar 7, 2008

I received this code from an example I found once upon a time that was originally submitted by someone else. Right now the code enters the current date in a cell of the same row as the checkbox when it's checked. I need to revise it, or come up with something similar, that will reference the value of a cell when it is in the same row as the button (from the Forms toolbar) that is clicked to activate the macro. First of all, I don't know how to reference a Forms button in VBA.

Sub Process_CheckBox()
Dim cBox As CheckBox
Dim LRow As Integer
Dim LRange As String
LName = Application.Caller
Set cBox = ActiveSheet.CheckBoxes(LName)
' Find row that checkbox resides in
LRow = cBox.TopLeftCell.Row
LRange = "B" & CStr(LRow)
'Change date in column B, if checkbox is checked
If cBox.Value > 0 Then
ActiveSheet.Range(LRange).Value = Date
'Clear date in column B, if checkbox is unchecked
Else
ActiveSheet.Range(LRange).Value = Null
End If
End Sub

Here is some other code I already created. Unfortunately, because I don't know how to do the row reference, I had to create 25 different macros, which just bulks up the size of my file and slows it down. But here is what I'm trying to accomplish in my macro:.............

View 8 Replies View Related

Command Button Click Creates New Rows Above Button?

Nov 8, 2012

I currently am using a form button to create new rows.

I need two different kind of buttons. I have a button that does this-

VB:
Sub InsertRow()
'this inserts a row where the button is clicked.
Dim r As Range

[Code]....

However, when I try and create these codes using a Command button (I need it for the color), I get the error

"unable to get the buttons property of the Worksheet class"

P.S one other thing, say the button is in row 3, and Cells A1 and A2 are merged, when you click the button in row 3, it should extend the merge to A3.

View 4 Replies View Related

Command Button - Create A Save Button In Sheet 1?

Mar 9, 2013

i want to create a save button in sheet 1, on clicking the same the data entered in the particluar cells of sheet 1 should get saved in sheet 2 in given format

View 1 Replies View Related

Form Button On Spreadsheet - Identifying Which Row The Button Is On

Jul 23, 2014

I am imagining a spreadsheet with the equivalent of an info button in each cell of column A. The idea is the user clicks the button to open a User Form containing additional info about the entitity represented by values on that row.

E.g., Row 1 is labels, row 2 is all about Bob, 3 is about Jane, 4 is about Freddy. If the user clicks the info button on row 3 then a user form opens up and displays additonal info about Jane.

I am wondering if there is a way for VB to identify which row the clicked button was on without having to write individual code for each button telling VB which row the button was on.

I am hoping there is some form button property I can reference or interrogate which will give me a cell address; a button.position property or similar. This will obviously save me a lot of coding if VB can dynamically determine which row is being interrogated as there will be a couple of hundred of these to start with, potentially thousands. (I realise Access is a better solution for database interrogation, but I have to work with excel at present).

Ultimately, the user form that the button calls up will have a list box on it and I want the list box to default to the entity on that row, and allow the user to browse the other entities if they want to.

If there isn't a solution for this I will just have a single info button for the users to click that opens the user form and then they can select the entity from a list box. But I would like to offer the "intelligent display" option but don't relish coding the equivalent of several hundred "You clicked button four hundred and seventy six, default to info card for bert".

View 4 Replies View Related

Running The VBA That Is Assigned To Multiple Cmd Button On One Cmd Button

May 19, 2009

I have a workbook that contains 18 sheets, on 14 of the work sheets I have a comand button that runs the following
Private Sub CommandButton1_Click()
' Range("B3:B53").Select
' Selection.ClearContents

Dim nloop As Long

Range("C3:K53").Select
Selection.Copy

Range("B3:J53").Select
ActiveSheet.Paste

For nloop = 3 To 53

If nloop 3 And nloop 12 And nloop 15 And nloop 16 And nloop 23 And nloop 28 And nloop 41 And nloop 47 Then Range("K" & nloop).Value = 0

Next nloop

Range("B2").Value = DateAdd("d", 7, Range("B2").Value)
Range("L7").Select
End Sub
I want to put a comand button on sheet18 that will run the code so it does the same thing as pressing each command button on each sheet.

the sheets that I have the cmd on are:
Sheet1, Sheet2, Sheets3, Sheet4, Sheet5, Sheet6, Sheet9, Sheet10, Sheet11, Sheet12, Sheet13, Sheet15, Sheet16, Sheet17

View 9 Replies View Related

Toolbar Command Button As Toggle Button

Aug 13, 2008

I have read several articles saying using a command button as a toggle button can't be done but some articles have said it is possible using the state properties.

.State = msobuttondown
.State = msobuttonup

Is there any way to combine this with the onaction property so that when you press the button, it stays down and activates a macro then when you press it again it pops back up and activates another macro.

View 8 Replies View Related

Button Not Accessible Until Another Button Pressed?

Mar 5, 2013

I'm trying to make it so that when I hit one button it unlocks the ability to hit two other buttons. I've been trying to do some OnEvent things but they just haven't been working for me.

Ideally I'd like it so that button B and C look like they shouldn't be pressed, perhaps translucent or grey, before button A is clicked, after that they should turn to another a specified colour.

View 7 Replies View Related

On / Off Button For Macro

Feb 20, 2014

How do i make a button run a macro on click, then undo the macros functions on the next click, like an on off button?

View 7 Replies View Related

Macro In A Button

May 19, 2009

i have macros which is attached in notepad and need to be called into 1 single button .

I have tried few ways but throwing an error.

View 6 Replies View Related

Button To Run A Macro?

Sep 3, 2013

I want to have every sheet with a "refresh" button, as all the sheets are populated with Webquery information, I want people to be able to go into the sheet and refresh the data.

What I need is a macro that will pull data from the sheet and insert it into a URL for example.

The site I'm pulling from looks like this in the macro:

URL;http://www.**REMOVED**.net/RestrictedPages/UserDetail.aspx?userID=44", _
Destination:=Range("$A$1"))

The "userID=44" section is also displayed within the data pulled, and sits in column A, so I need something like this:

URL;http://www.**Removed**.net/RestrictedPages/UserDetail.aspx?userID=Range("A2").Value", _
Destination:=Range("$A$1"))

But Visual Basic gives me a error and highlights the entire section in red.

View 9 Replies View Related

Have Button Run Macro

Dec 29, 2006

I am new to insert buttons and I i figured how to drag one and put it in the toolbar at the top to perform a macro when it is pushed. I saw somewhere where someone had a button in say A1 that performed a certain macro and then one in A2 that performed another macro. how to insert a button in both of these cells that I can link to a macro to insert data into a specified cell?

View 4 Replies View Related

Uncheck Check Box + Check A Option Button Using Command Button

Aug 20, 2009

When I press a command button I want to:

Uncheck a checkbox made with Control Toolbox

Check a particular Option Button within a group box made with the Forms Toolbar.

EDIT: Clarification, I want both things to happen when I click one button.

View 3 Replies View Related

Macro Doesn't Work On Button, But Fine From Macro Menu!

Dec 15, 2008

I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.

When I run the macro from the Tools>Macros menu it works perfectly.
But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.

The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.

Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!

View 12 Replies View Related

Run Macro From Command Button In Userform - Display Msgbox At The End Of Macro

Apr 17, 2014

I have a userform with a command button which fires a macro.

everything works fine so far.

my problem is:

I would like to add a msgbox at the end of the macro which confirmes "successfully completed".

I cannnot simply add the msgbox at the end of the macro. don't know what I'm doing wrong.

(see below)

Private Sub CommandButtonOK_Click().
If Me.OptionButton1.Value = True Then
ThisWorkbook.Worksheets("PropertyWorksheet").Range("A1").Value = "Government Securities"
ElseIf Me.OptionButton2.Value = True Then
ThisWorkbook.Worksheets("PropertyWorksheet").Range("A1").Value = "Corporate Bonds"

[Code].....

Unload Me

Application.ScreenUpdating = False
Sheets("MySheet1").Select
Application.ScreenUpdating = False
'run macro
MyMacro1 (adds, hides and deletes various sheets)
MyMacro2

[Code]....

View 2 Replies View Related

Add Button On Toolbar For A Macro

Oct 5, 2012

I have a macro and I want a button on the toolbar, once clicked on the button, I want the macro to run.

Is there any way to do this. If this can be done using add-ins, I doesn't know how to create a button for the same.

View 4 Replies View Related

Protect A Button Containing A Macro?

Dec 4, 2008

I have button on a worksheet that has a macro assigned. I need other people to see the workbook, but I want the button DISABLED for them to click, but able for me to click it. I have written in the macro to protect the sheet once it has run, but the button is still pressable.

View 2 Replies View Related

Macro Button On Desk Top?

Sep 9, 2009

I need a thrid party program to be able to run excel and word macros from my desk top? I can build the buttons but I would like to make a tool bar that I can place on my desk top, I have about 15 macros I use all the time.

I just had a second thought well not mine my daughter, she suggested that I use a gaming keyboard which already has extra keys. She has one, it has 15 extra keys. Has anyone ever tried to program macros to something like this? I still prefer having a tool bar on my desk top but it is a creative idea. I just don't know if Excel or Word would recognize the extra keys.

View 4 Replies View Related

Print Button Macro ..

Nov 24, 2008

I have a workbook with approximately 260 sheets. These sheets contain financial information. They have to be sorted according to the division within the company, with a total page for each financial division.

I have a code on a blank tab that prints the sheets based on a value in cell B3 on each sheet. (i.e. "VB", "WB", which are initials of our district managers.)

View 6 Replies View Related

Macro For Button On Worksheet

Apr 6, 2009

I am trying to make a button that puts the formula back into the cell after a manual entry

View 3 Replies View Related

Macro To Be There In Single Button

Apr 7, 2009

I have a macro which was developed with the help of our Experts.

I need all that macros to be in 1 button would that be possible.

I have tried this but not able to make it.

View 12 Replies View Related

Macro In A Toggle Button..

Jul 27, 2009

it's possible to use this macro code in a toggle to perform this action when it's true and when the toggle is click again it can undo it.

This macro does lookups and finds the max on sets of value when it is run and i was wondering if by clicking the button again it can undo what it placed in the cells.

View 4 Replies View Related

Run A Macro From A Command Button

Dec 20, 2009

I have created a command button from the Excel 2003 from the Macros toolbar that I placed on my worksheet. I want to use this button to activate an existing macro. Currently I have no code entered for this button and the button when clicked on behaves as an object to be edited, moved, etc. How should I proceed?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved