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
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
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
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
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
Feb 22, 2008
How do I make this formula include searching for "ann" and "john" (in separate cells)?
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
