Search results

Hi, fulltime, sorry about the misunderstanding. There is no way to my knowledge as the cell value gets shortened if there is anything (value or formula) inslde the next cell to the right. And the option Shrink To Fit will display the contents in no really readble height... The only way I know...

Hi, fulltime, either drag the colum by holding the right borderline with the left mouse button pressed to the desired width, double click the border of that cell at the top line where the columns are indicated (cell pointer changes from an arrow to a different pointer which points into 2...

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