Posts by HaHoBe
-
-
Re: trying to activate sheet based on cell value
Hi, rvkcools,
assign a value to tempname otherwise the variable will show ab emnpty sting which can´t be found to to contents in the cells compared.
Maybe try it like this
Code
Display MoreSub emptest() Dim tempname As String Dim lngCounter As Long tempname = "test" With Worksheets("team") For lngCounter = 24 To .Cells(Rows.Count, "A").End(xlUp).Row If .Cells(lngCounter, "A").Value = tempname Then Worksheets(tempname).Select Exit For Else MsgBox "no sheet " & .Cells(lngCounter, "A").Value End If Next lngCounter End With End Sub
Ciao,
Holger -
Re: Keep a running total in a single cell
Hi, basshunter514,
in case of a formula you must either relate in the cells in which the entries are made directly or to the Calculate-event to catch if any new calculation in a range has been made. This might require a volatile function in the sheet as well.
Ciao,
Holger -
-
Re: Set print area based on a cell value with vba
Hi, mikejaco,
some parts of your code must have gone while copying.
I can´t figure out a reason to start at the top for the loop (I´d preferred to start from the bottom up as the last will overwrite any existing pagesetup). Code may be applied to the Workbook_BeforePrint(Cancel As Boolean) event in ThisWorkbook and may be altered to reflect a certain sheet or find out the last used row on that sheet:
CodeDim lngCounter As Long For lngCounter = 0 To 10 If Cells(23 + lngCounter * 25, "A").Value > 0 Then ActiveSheet.PageSetup.PrintArea = Range(Cells(1 + lngCounter * 25, "A"), Cells((lngCounter + 1) * 25, "K")).Address End If Next lngCounter
CodeDim lngCounter As Long For lngCounter = 23 To 223 Step 25 If Cells(lngCounter, "A").Value > 0 Then ActiveSheet.PageSetup.PrintArea = Range(Cells(lngCounter - 22, "A"), Cells(lngCounter + 2, "K")).Address End If Next lngCounter
Ciao,
Holger -
-
Re: Is there a way to create code that will insert a formula within a spreadsheet?
Hi, legaltrends,
you could use the Worksheet_Change-Event of the worksheet and limit it to just column C. If anything is entered into a cell in column C the following code is triggered (sorry, didn´t get your formula in G to work - this part of the code is missing here):
Code
Display MoreOption Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Columns("C:C")) Is Nothing Then Exit Sub Application.EnableEvents = False With Target .Offset(0, 1).Value = Left(Target.Value, 1) .Offset(0, 2).Value = Mid(Target.Value, 2, 1) .Offset(0, 3).Value = Mid(Target.Value, 3, 1) End With Application.EnableEvents = True End Sub
No formulas should make up a bit of speed...Ciao,
Holger -
Re: Show/Hide toolbars for current workbook only
Hi, GuyGadois,
you may have a good look at ThisWorkbook and the Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window) and Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window) events to call your codes when changing workbooks.
Ciao,
Holger -
Re: get file\properties\autor & otganizastion by vba
Hi, DROM,
you should look for the BuiltinDocumentProperties of the workbook.
Code
Display MoreOption Explicit Sub BuiltinDocumentProperties() Dim intI As Integer Dim obj As Object intI = 1 Sheets.Add after:=Worksheets(Worksheets.Count) ActiveSheet.Name = "DocumentProperties-" & Format(Date, "YYMMDD") For Each obj In ActiveWorkbook.BuiltinDocumentProperties Cells(intI, 1).Value = obj.Name On Error Resume Next Cells(intI, 2).Value = obj.Value intI = intI + 1 Next obj Columns("A:B").EntireColumn.AutoFit End Sub
Code will add a new worksheet at the end of the workbook, change the name, amend the date and list the properties where filled in.Ciao,
Holger