Search results

Hi, mdjks, as the code will to be in a saved workbook you could try: With Sheets("Sheet1").Range("G19") If .Value = "" Then MsgBox "Fill in missing value in Cell G19 on Sheet1", vbExclamation, "Can´t save" Exit Sub End If ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" _...

Hi, spock1971, sorry but I neither know your plans nor your workbook/worksheets or even how and what each button (on on each?) should be doing. You can have one button to do all the work, you can have one on each sheet. This depends on what should be done next. If you already know that only a...

Hi, spock1971, tell the excel-event whatelse than showing a messagebox it should do (which should be call the macro in a regular module): Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "$j$1" Then Exit Sub InsertPicture End Sub Ciao, Holger

Hi, Ian, I don´t like the idea very much: 'ClassModule: ThisWorkbook Option Explicit Private Sub Workbook_Open() ' Hans W. Herber, 011898 Dim oBar As CommandBar Dim oButton As CommandBarButton On Error Resume Next Application.CommandBars.Add("My Commandbar").Delete On Error...

Hi, spock1971, an example may look like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "$B$3" Then MsgBox "no macro" Exit Sub End If MsgBox "put macro here" End Sub The above is only for showing the working of the code and could be shortened to...

Hi, spock1971, as far as I know no chance with Indirect. Maybe you´ll have a look at A VBA Function to Get a Value From a Closed File or Copy a range from closed workbooks (ADO) or Different approaches to retrieve data from underlying workbooks for that purpose. Ciao, Holger

Hi, spock1971, take that option and you´re inside the VBE right where you want to be: inside the code window you will see two dropdowns. From the left one choose Worksheet. According to that selection only the responding events will be listed in the right one where you than can find Change...

Hi. spock1971, something like this: Option Explicit Sub StoresMeetCriteria() Dim lngLastRow As Long Dim lngCounter As Long Dim lngArray As Long Dim myArray() As Variant Dim strMessage As String lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row For lngCounter = 2 To lngLastRow If...

Hi, spock1971, shall I answer three times as well? ;) (only kidding) Use the Worksheet_Change-Event of the sheet to trigger the macro. In there you have to restrict the area to just and only the cell of the dropdown. You can refer as Target to it in the code later on. Regarding the size of...

Hi, spock1971, certainly there a place with both the information of the names of the stores as well as the date of opening. This information can be read in to an array (well only the information to fit the criteria and by using ReDim fo rthe correct number in the array) which can be used for...

Hi, Matt, as this would long for VBA to handle I would prefer to present a Userform instead of Data/Validation and handle everything from there in VBA... ;) Ciao, Holger

Hi, Matt, are you looking for something like this: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("F:F,H:H")) Is Nothing Then Exit Sub If IsNumeric(Target.Value) Then Select Case Target.Column Case 6 MsgBox "Only enter text...

Hi, Matt, if you want the message just to appear on a certain sheet enter the following code behind that sheet: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub MsgBox "Please create Hyperlink to answer" End...

Hi, Paul, as I may have misunderstood you please check out the following code on a copy of the original file. Option Explicit Sub elgoober() ' assumes the list to be in Column A of the sheet ' furthermore list starts in Row 1 with a heading, ' first pair of information to be found in Row 2...

Hi, spock1971, maybe assign the following code to a button and have a list according to data/validation in cell B1 (if you use different names or do not include the path for the file the code has to be altered): Option Explicit Sub InsertPicture() ' Hans W. Herber, 203503 ' expects the path...

Hi, Matt, sorry I only know the way by VBA (I´m sort of limited in that ;)) Ciao, Holger

Hi, Matt, otherwise use Controls("TextBox" & bytTextBoxCounter).Enabled = False Ciao, Holger

Hi, net, I pretty much doubt the formula not to work - I would have guessed =IF(J1487=0,"",K1487-J1487) Ciao, Holger

Hi, Matt, as long as the suitable place for adding is directly under the last entry in Sheet1 everything will be fine. Else you would need some VBA and a UserForm... ;) Ciao, Holger

Hi, Matt, ever thought about freezing panes? The hard way would be VBA but here I´d give the easy way a try... ;) (tried it with Excel97 and it worked). Ciao, Holger