Edit ComboBox Code

Jan 5, 2007

I have a list of worksheet names in a combobox, when selected using the drop down arrow it takes you to that worksheet. The problem is say I select "ABC" from the list and it takes me to the "ABC" worksheet but when I go back to the summary page the "ABC" is already selected in the drop down box and I cannot click it any more. Is there anyway to reset the combo box so that it goes back to the first selection of the list?


Private Sub AFISGBox_Change()
Dim strSheet As String
If AFISGBox.ListIndex > -1 Then
strSheet = AFISGBox
Sheets(strSheet).Select
End If
End Sub

View 9 Replies


ADVERTISEMENT

Edit Combobox Entries From Within Itself

Feb 19, 2007

I have a combobox that populates a dynamic list, but is it possible to also edit or delete those items from within that same combobox?

View 2 Replies View Related

Dismiss ComboBox Dropdown While ComboBox Code Is Still Running

Dec 8, 2009

When I make a comboBox selection from the dropdown menu, the menu stays down until the last statement of the comboBox code is finished. How do I make the dropdown disappear immediately after the selection is made?

View 9 Replies View Related

Edit VBA Vlookup Code

Jun 3, 2009

Right now i have two worksheets:
"900 - Reel"
"OverRides"

On the "900 - Reel" sheet i have a userform ("userform4") open when double clicking a specific cell. Upon opening it uses the following code to go to sheet "OverRides" to find two specific values and imputs them into "textbox1" and "textbox2" on "userform4"

View 4 Replies View Related

Edit Undo When Using VBA Code

Jul 21, 2006

Is it possible to enable the undo command when using VBA code in a spreadsheet? Right now if someone makes a mistake it can’t be undone.

View 5 Replies View Related

Edit Code To Add Extra Criteria

Feb 22, 2008

How do I make this formula include searching for "ann" and "john" (in separate cells)?

View 2 Replies View Related

VBA Code For Saving PDFs To Edit Name

Jun 24, 2014

I'm working on a mac and have a macro that will go through all the sheets in my workbook and save them as PDFs to a specific location.

Sub CreatePDF()
For sh = 19 To Sheets.Count
Sheets(sh).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"/Users/mikemahoney/Desktop/Publisher Payables/Statements/June 2014" & Sheets(sh).Name & "June 2014 Revenue Share Statement" & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
Next
End Sub

The part in bold is obviously where I am saving the PDFs but it is also being included in the naming of the file. Need replacing the red text in the naming of the file with the contents of cell B9? I would still like to save the PDF to "/Users/mikemahoney/Desktop/Publisher Payables/Statements/June 2014" I just don't want the file-path to be included in the name of the file.

Also, this code seems to run into errors if a sheet is hidden, hence starting at sheet 19. Any way to tweak this code to skip over hidden sheets?

View 1 Replies View Related

Edit Chart Code To Exit If

Oct 13, 2008

I just wonder if we can have the code to exit if G2 returns an even number, as 2, 4, 6 etc?

Sub Listbox3_Change()

If ActiveSheet.Index 1 Then Exit Sub
Application.ScreenUpdating = False

With Sheets(1)
.Unprotect "mypsw"
.Range("G2").Value = ActiveSheet.ListBoxes(Application.Caller).Value
With .ChartObjects("Chart 2").Chart.Axes(xlValue)
If VBA.VarType(Sheets(1).Range("D1").Value) = VBA.vbError Then Exit Sub
If VBA.VarType(Sheets(1).Range("E1").Value) = VBA.vbError Then Exit Sub

.MinimumScale = (Sheets(1).Range("D1") - 0)..............

View 9 Replies View Related

Edit Code From The Macro Recorder

Jul 25, 2006

The way i have been creating macros is by going to the tools menu.....macro....then..... record new macro.

I have a file which I have re-formatted using a macro as described above however because i receives files every month to do updates every time i open a new file and try to perform that same macro it either wont work or it wont format the correct rows.......is VBA the solution to this????

View 9 Replies View Related

Code To Edit Macro Security Settings

Apr 1, 2008

I need a code to change the Macro security settings of the computer where the file is opened to enable macros.

View 9 Replies View Related

Edit VBA Code For Lotus Note To Display Email Instead Of Sending Out?

May 28, 2013

below is the code i use to create a lotus note email.

i would like it to display my email and send it manually instead of sending out automatically.

' Open and locate current LOTUS NOTES User
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName

[Code].....

View 4 Replies View Related

Combobox Without VBA Code

Aug 12, 2006

I’ve found an excel sheet (witch a sample I post) from a Hobby I have, and after looking how it work’s it occur to me, that a similar file would solve I big problem in the restaurant I work. I’ve been thinking about it and I’ve now all the layout of my new-to-be file in my head, and I was going to start working on it, when I found that I couldn’t do one of the basic think on this file. Witch is to make the combobox were you can change the text according to a number or pre-determinate options. I know that you can use VBA to add combobox’s to you excel sheet, but this doesn’t seem to be the case. You can see the example in the cell B4 in the file that I post here.

View 5 Replies View Related

Combobox Code Is Not Working

Nov 17, 2011

THe below have written for comboBox selection when I run thourgh form and commandButton code is not working but when run single (Seperately) code will get execute.

Private Sub CommandButton1_Click()
Dim Mydate As Date
Mydate = Date

[Code].....

View 1 Replies View Related

ActiveX Combobox Code-run

Jun 4, 2009

I've created a workbook on whose main sheet there are three activex comboboxes. The first one contains a list of years, and the second one contains a list of months. The user selects the year, and it goes to its linked cell. The user then selects a month, and using the Combobox_Click event, should run a sequence of 7 macros (i.e. using the "call updateinformation1" macro, which is the first of the seven updateinformation macros).

The sequence of macros ran fine before I added the comboboxes. After adding the comboboxes, the final (7th) macro somehow goes back to the first macro and then causes a "ClearContents method of Range class failed" error on the "Selection.ClearContents" line. These are the first and seventh macros:

Sub updateinformation1()
'clears previous information to make way for new information
Application.ScreenUpdating = False
Application.EnableEvents = False

Worksheets("Criteria2").Activate
Cells.Select
Selection.ClearContents

I can't for the life of me figure out why it's looping back to the first macro instead of just stopping after the seventh. I have other workbooks that do something similar, and I don't have this problem. Am I missing something?

if I need to post all 7 macros?

View 9 Replies View Related

How To Code Combobox To Display Several CSV Files

Jul 9, 2014

I am coding my first UserForm. I've gotten some of it working. I need to display a ComboBox that will display 3 different CSV files. Since these will be updated here and there, I didn't think AddItem would work. How i should script this?

View 2 Replies View Related

ComboBox Code For Enable & Disable

Oct 13, 2008

I started to write mini codes but still much to learn.

Private Sub ComboBox3_Change()
If Range("A1") = "TRUE" Then
ComboBox3.Enabled = 1
ElseIf Range("A1") = "FALSE" Then
ComboBox3.Disabled = 1
End If

End Sub

View 9 Replies View Related

ComboBox Change Code Looping

Jan 10, 2007

I have a piece of code attached to a combo box. When selected the code loops through 3, even 4 times for no apparent reason. Has anyone come across this before? The loop can start either after the final "end sub" or part way through (.clearcontents mainly) Have included the code just in case it is something obvious.

Private Sub cmbDI_GrowthBasis_Change()
If UCase(Worksheets("Tables"). Range("GrowthNo")) = "NIL" Then
Worksheets("Detail Inputs").Range("DI_GrowthRateTitle") = ""
With Worksheets("Detail Inputs").Range("DI_GrowthRate")
.ClearContents
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Interior.ColorIndex = 15
.Locked = False
End With
Else
Worksheets("Detail Inputs").Range("DI_GrowthRateTitle") = Worksheets("Tables").Range("GrowthNo") & " :"
End If
End Sub

View 2 Replies View Related

Combobox Code Is Kicking In Unexpectedly

Jun 28, 2008

On my master screen I click a button that invokes my Housekeeping routine. This involves setting up RecordSets, populating 3 ComboBoxes and displaying my Housekeeping Form.

This is populated and displayed as follows :

Sub Load_KA_Housekeeping_Form()

'Load options into KA_Housekeeping_Form ComboBox

KA_Housekeeping_Form.KA_Housekeeping_ComboBox1.AddItem "Add New League"
KA_Housekeeping_Form.KA_Housekeeping_ComboBox1.AddItem "Delete League"
KA_Housekeeping_Form.KA_Housekeeping_ComboBox1.AddItem "Amend League"

Once displayed, the 3 ComboBoxes on the Housekeeping form all have _Change() code that kicks in depending upon which ComboBox I make a selection from … this works great and is what I have tried to use elsewhere … BUT …

When I select the relevant action from the ComboBox in question (in this case it is Rename Team), what WAS happening was that a ComboBox on my next form would be populated & the form would be displayed. I then made a selection from that ComboBox and clicked OK, where I have _OK_Click() & _Cancel_Click() code in place.

I thought it would be a good idea here, to do as above, and once I have made a selection from this ComboBox, the code would kick in automatically, rather than me having to click OK. However, this is where it gets weird … when the routine is called to populate the ComboBox, it initiates the _Change() code for the new userform, even though the form has not yet been displayed!

This is populated differently, as follows :

Case Is = "Rename Team"
EndOfLoop = NumberOfLeagues
FillTeam_Data = "Leagues"
Call Fill_Team_ComboBox(KA_Team_Rename_Form.KA_Team_Rename_ComboBox1)

Sub Fill_Team_ComboBox(MyComboBox As MSForms.ComboBox)

MyTeamSub = 0

Now, yes, I can see that I am updating and therefore "changing" the ComboBox, but I don't understand why it works using the first method but not the second, I don't understand why code for a form is being invoked before the form has been displayed !!!

I fully understand that it may well be me, I am a self-confessed beginner at VBA, but if somebody could please explain or put me straight I'd be grateful ...

View 11 Replies View Related

VBA Code - Exit Combobox By Hitting Tab And Enter

Aug 10, 2013

I have a problem here regarding the combo box. The whole Column A has combobox. It would be easier if by just hitting the TAB or Enter it would go to the next cell just like what is usual in excel.

TAB = next cell to the right
Enter = Next cell below

View 1 Replies View Related

Data Validation With ActiveX ComboBox With VB Code

Sep 19, 2013

I found a code on [URL] that claims that it will do what I want to achieve. I tried it but it is not working for me. Here is the code

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String

[Code]....

How to verify that this code is a working code and not just bogus one?

View 1 Replies View Related

Add Combobox Or Listbox For Assigning Criteria Code?

Mar 22, 2014

If Sheets ("Sheet1").Cells (i ,"D").Value="January" Then ...this is my code line.I would like to replace my criteria"January" with a listbox or combobox with months names so that my out put results vary according to my list selection from the combobox., so that I need not to change or edit my criteria value every time according to my requirement.Is it possible in vba?

View 4 Replies View Related

Combobox List - Pivot Items Selection Code

Nov 23, 2006

I have a userform, on the user form I have a combo box. when i select an item from the combobox list. I want it to show only that item in the pivot table.

Code:
Dim i As Integer
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Description")
For i = 1 To .PivotItems.Count
If i = ComboBox2 Then
.PivotItems(i).Visible = True
Else
.PivotItems(i).Visible = False
End If
Next
End With

View 9 Replies View Related

Adding Loop Code To Userform Combobox List?

Apr 27, 2011

I have 10 comboboxes - all require exactly the same list. Rather than having to copy the list 10 times in the coding - and changing the combobox name from listcode1, listcode2 etc, is there a loop code which I can add to do this for me??

Private Sub Userform_Initialize()
'Empty txtdate
txtdate.Value = ""[code].....

View 9 Replies View Related

Running Code Based On Option Selected In A Combobox

Mar 26, 2007

I have set up a combolist box, and wish to run different VBA subroutines depending on what the user has selected from the combolist. How do I do this? Apologies in advance if this sounds like a silly question.

View 2 Replies View Related

Clear Method Range Class Failed - Combobox Code

Apr 23, 2014

I have two separate workbooks, the first is called Job test and is to be used as a template for quoting jobs, the second is called Fixtures and is a database of fixtures that are organized in table. I keep them separate as multiple jobs will use the Fixtures DB workbook and I want to be able to update it and add new fixtures in one area. In order to facilitate this I have a macro that opens the Fixtures DB workbook anytime that the Job test workbook is opened. In the Job test workbook I have multiple dropdowns that I hope to make dependent or cascading by means of filtering the Fixtures DB workbook. The issue I have run into is with the following code.

Code:
Private Sub CLightType_Change()'Filters LampType
Workbooks("Fixtures.xlsm").Sheets("CLampType").Activate
Workbooks("Fixtures.xlsm").Sheets("CLampType").Cells.Clear
Workbooks("Fixtures.xlsm").Sheets("Fixtures").Cells.AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
:=Workbooks("Job test.xlsm").Sheets("Criteria").Range("A1:A2"), CopyToRange _
:=Range("A1"), Unique:=False
[Code] ......

What this code hopes to achieve is that when I change the value in the LightType dropdown, the Fixtures DB workbook will automatically filter the data once to a different sheet(CLampType), then get only unique values for LampType in column O. I have set up a dynamic range for column O so as to populate my next combobox, LampType.

This should all be fairly simple and straightforward, however I am running into "Run-time error '1004': Clear method of Range class failed." when I try to execute the line to clear the worksheet, and also have an error when I try to filter the data via macro. The strange part is all of this can be done manually without a problem, and moreover I have tried recording the process and using the recorded version. Even stranger yet is that when I add an "on error resume next" before everything, the code works fine but keeps looping and acts finicky(I don't want to simply resort to this as a solution). I have also tried setting this macro up inside the Fixtures workbook instead and calling it from the combobox change, to no avail.

View 1 Replies View Related

Mouse Wheel Scroll In A Combobox On Userform (Lots Of Code)

Sep 16, 2007

I have seen some code around that allows scrolling in listboxes using the mousewheel. I have also seen some code here on the forums about scrolling through a combobox embedded in a workbook. I have tried adapting both of these to suit a combobox on a userform but I cannot get either to work.

Here is the code that I used for the embedded combobox; I added it to the base code for the userform

I then added the following code to a standard module as stated in the forum post;

In the first section of the code there is a msgbox that never gets shown so I don't think code is recognizing the mouse wheel at all.

The second set of code that I used was set up to work with a listbox and it works perfectly with a listbox control. The problem is I cannot adapt it to suit a combobox and if I use a breakpoint to see what is happening Excel freezes.
The following code is added to the userforms code;

Then in a standard module I added the following code;

I added this section of the code;

I think this should only work on a Combobox but a Combobox does not appear to support the TopIndex value. Is there a way this can be setup to work with a userform combobox?
Sorry about the huge amount of code.

View 9 Replies View Related

Amend VBA Code To Change Mandatory Inputbox To Userform Combobox Entry

Apr 25, 2013

I have 4 columns, If column B (Ref No) is filled in, the adjacent columns, C,D and E become mandatory and an input box pops up on screen one after the other for entry into each column.

My problem is that I require a drop down list and not a input box for the final column (status). So i have created a userform with a combobox dropdown. I am having problems connecting the combobox selection to the final column, and for the selected combobox item to go into the right cell like the input box entry currently does.

Attachment 53209test743.xls

I have attached a sheet and also you can see the code below.

Code in Sheet

VB:
Public SaveVal1
Public SaveVal2
Public SaveVal3

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

[Code] .....

View 3 Replies View Related

VBA Code To Change Pivot Table (Report Filter) From Userform Textbox / Combobox

Aug 16, 2013

I've prepared an excel file with a pivot table. Now I would like to change the Pivot "Report filter" by using Textbox on Userform. I've attached an excel file as an example.

View 2 Replies View Related

Populating Combobox 2 With Items That Match Criteria From Combobox 1

Mar 30, 2009

Am trying to get dynamic population of 2nd combobox based on match from criteria in combobox 1.

if column a = bears and column b = colours of bears then

when I select bears in combobox one, combobox 2 would populate with colors of bear.

I am think of having a combobox 1 change event that evaluates each row in a specific range (does it match the criteria?) if so, then add 2nd cell (column b) of that row to the combobox 2.

I know it would probably involve match and offset, add item and loop, but I am not sure what the syntax is.

View 9 Replies View Related

Auto Populate 2nd Combobox Based On 1st Combobox Data

Mar 30, 2014

I need to populate two combo boxes from excel sheet, the data will be like below:

Column A Column B
A 1
A 2
A 3
A 4
A 5
B 100
B 101
B 102

So from the above data, one combo box should hold unique values A & B.

On selecting a value from the 1st combo box A or B, respective values should be populated in 2nd combo box.

So the data should be like below:

If A is selected in the 1st combo box, then 2nd combo box should only show the values 1,2,3,4 & 5.
If B is selected in the 1st combo box, then 2nd combo box should only show the values 100,101 & 102.

Friends I need it in a macro and one important point is, this is dynamic and it is not static and the data can be more.

View 1 Replies View Related







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