Posts by HaHoBe

Re: worksheet_selection change event

https://www.mrexcel.com/forum/…ction-change-formula.html

Re: Renaming Sheets and Coding

Hi, S O,

please clarify:

Quote

You can also set the worksheet in memory, then the name doesn't matter

You would need the correct sheet name in order to get your sample code working.

If you rely on the codename you must not set an object to that codename as it already exists and may be addressed.

Sample:
your code works fine if the sheet is named "Data". You would need to alter the code if soembody renamed the sheet to "Test". Am I correct about this?

At any case if the codename for the mentioned sheet was Sheet1 the code would work, no matter how the sheet would be named on the worksheet tab.

Ciao,
Holger

Re: Renaming Sheets and Coding

Hi, oracle259,

as long as you stay in the very same workbook you should encounter no problem in not using the sheet name like it is indicated on the worksheet tab, i.e.

Worksheets("Data").Move After:=Worksheets(Worksheets.Count)

which would move the names worksheet after all worksheets.

You could look up the codename for the worksheet (assuming this is Sheet1) and use

Sheet1.Move  After:=Worksheets(Worksheets.Count)

This would move the sheet with the codename no matter what will be displayed on the worksheet Tab.

The codename for a sheet may only be altered in the VBE via properties or by VBA code.

HTH,
Holger

Re: Worksheet Change not doing anything

Hi, Red Smurf,

please try

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Count > 1 Then Exit Sub


If Range("C12").Value <> "" Then
  If Not Intersect(Target, Range("D15:D39")) Is Nothing Then
      Application.EnableEvents = False
      Range("F" & Target.Row).Value = Sheets("Email Check Workings").Range("F" & Target.Row).Value
      With Range("C" & Target.Row)
        .Value = Sheets("Email Check Workings").Range("C" & Target.Row).Value
        .HorizontalAlignment = xlRight
      End With
      Range("H9").Value = Target.Row
      Application.EnableEvents = True
    End If
End If


End Sub

where the first check for C12 could be swirtched with teh Intersect line.

Ciao,
Holger

Re: Worksheet Change not doing anything

Hi, Red Smurf,

maybe change your code to read

Private Sub Worksheet_Change(ByVal Target As Range)


Dim i As Integer


For i = 15 To 39
    If Sheets("Email Check").Range("C12").Value = "" Then
    
    ElseIf Sheets("Email Check").Range("D" & i).Value = "" Then
    Else
        Application.EnableEvents = False
        Sheets("Email Check").Range("F" & i).Value = Sheets("Email Check Workings").Range("F" & i).Value
        With Sheets("Email Check").Range("C" & i)
            .Value = Sheets("Email Check Workings").Range("C" & i).Value
            .HorizontalAlignment = xlRight
        End With
        Sheets("Email Check").Range("H9").Value = i
        Application.EnableEvents = True
    End If
Next i


End Sub

Although I would prefer to narrow down the range in which to start the procedure by using

If Not Intersect(Target, Range("D15:D39")) Is Nothing Then

Ciao,
Holger

Re: help with Cobo boxes in Excel 2010 + VB

also posted here: http://www.mrexcel.com/forum/e…-box-excel-vbulletin.html

Re: If Row 1 = Row 2 then delete row 2, else msgbox

http://www.mrexcel.com/forum/e…te-row-2-else-msgbox.html

Re: VBA Dynamic No. Of Column Headers

Hi, ir121973,

change to read

For Each rCell In Range("C7", Cells(7, Columns.Count).End(xlToLeft))

Ciao,
Holger

Re: Paste values under certain number of rows with worksheet array loop

Hi, patel,

could you please elaborate on

SpecialCells(xlLastCell, xlNumbers)

Ciao,
Holger

Re: VBA - copy/paste range of cells every nth column

Hi, mfields,

maybe you can work with this code as a start:

Sub OZ183658()
Dim lngCounter As Long
Dim lngTarget As Long


lngTarget = 29
For lngCounter = 1 To 444 Step 9
  Cells(lngTarget, "N").Resize(23, 9).Value = Cells(2, lngCounter).Resize(23, 9).Value
  lngTarget = lngTarget + 24
Next lngCounter


End Sub

Ciao,
Holger

Re: Changing fileformat when saving?

Hi, stildawn,

if your actual workbook is in a newer format CF will be cut down to the limitations for workbooks in xls-Format (only 3 conditions) as well as other restrictions reagrding formulas, pivots, and more. As far as I know you can turn the checking off in the workbook you want to save.

HTH,
Holger

Re: Changing fileformat when saving?

Hi, stildawn,

maybe like this:

Sheets("Air").Copy
ActiveWorkbook.SaveAs Filename:="N:\National Share Drive\1 CSU Homepage\CS Interactive Reports\Formatted Reports\" & strClientName & "-Air-" & Format(Date, "dd.mm.yy") & ".xls", FileFormat:=xlExcel8
ActiveWorkbook.Close False

Maybe consider to use the formatting like yy_mm_dd instead of the format you used.

Ciao,
Holger

Re: I want a macro that will not mess up if rows are deleted...

Hi, moore.1671,,

no need to quote the full post.

The code does not go behind Sheet("Statement") but goes into ThisWorkbook in order for the event to get fired.

You should copy the code from here and paste it into ThisWorbook as the code you used should raise an exception.

Ciao,
Holger

Re: I want a macro that will not mess up if rows are deleted...

Hi, moore.1671,

please wrap your cod ein tags.

Maybe your code could be simplified to

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Sheets("statement")
  If .Range("H84").Value = "" Then
    MsgBox "Please enter a Prior Month Premium"
    Cancel = True
  End If
  If .Range("J86").Value = "adjustment" And .Range("C82").Value = "" Then
    MsgBox "Please explain the difference between prior and current month premiums before saving."
    Cancel = True
  End If
End With
End Sub

because I doubt a loop through a single cell will not really be help.

Ciao,
Holger

Re: Copy Range to new worksheet, consequetively

Hi, Walter ,

maybe try it like this:

Sub OZ182445()
Dim wsOrig As Worksheet
Dim wsNew As Worksheet
Dim lngCounter As Long


Set wsOrig = ActiveSheet
With wsOrig
  For lngCounter = 1 To .Range("A" & Rows.Count).End(xlUp).Row Step 5
    Set wsNew = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    .Range("A" & lngCounter).Resize(5, 1).EntireRow.Copy wsNew.Range("A1")
    Set wsNew = Nothing
  Next lngCounter
End With
Application.CutCopyMode = True
Set wsOrig = Nothing
End Sub

Ciao,
Holger

Re: Next open row

Hi, JoeHelpexcel,

maybe try using a boolean variable to determine whether a user has started to write data and disable the button for all other users for the time until the process is finished (any relational database will deliver this behaviour as a standard without the other disadvantages of a sheared workbook).

Ciao,
Holger

Re: Next open row

Hi, JoeHelpexcel,

the code posted here should do okay for a single entry from each control - I don´t get the idea why you are talking about multiple entries being saved on the same row. Can you please elaborate or attach a sample workbook?

Ciao,
Holger

Re: Next open row

Hi, JoeHelpexcel,

the cod eyou posted seems to work fine -w emay only test with samples we created and will differ from you original workbook. Assuming the textboxes and ComboBoxes are filled you would need to elaborate if you want to close the workbook (then you can´t quit Excel as the macro stops right when the workbook is closed) or quit Excel as well.

Private Sub saveclose_Click()
Dim nr As Long
With ThisWorkbook.Sheets("sheet1")
  nr = .Cells(Rows.Count, 1).End(-4162).Row + 1
  .Cells(nr, 1) = Me.TBDate
  .Cells(nr, 2) = Me.CmbListItem
  .Cells(nr, 3) = Me.TBUser
  .Cells(nr, 4) = Me.ComboListItem2
End With
ThisWorkbook.Save
''closes workbook with save but ends macro
'ThisWorkbook.Close SaveChanges:=True


''terminates instance of Excel
'Application.Quit
End Sub

Ciao,
Holger

Re: Next open row

Hi, patel,

Excel2013 shows -4162 as the equivalent of xlUp - any explanations from your expertise for a better understanding from my side?

Ciao,
Holger

Re: Check to see if 2 workbooks open and if yes exit sub

Hi, Jay,

why exit the sub? In use by you or any other user? If by you/user in the same instance you may like to try

Set wb1 = ThisWorkbook
    For Each wb In Workbooks
      Select Case wb.Name
        Case "Workbook 2.xls"
          Set wb2 = wb
        Case "Workbook 3.xlsx"
          Set wb3 = wb
      End Select
    Next wb
    If wb2 Is Nothing Then wb2 = Workbooks.Open(Filename:="C:\Users\Desktop\Workbook 2.xls")
    If wb3 Is Nothing Then wb3 = Workbooks.Open(Filename:="C:\Users\Desktop\Workbook 3.xlsx")

Ciao,
Holger