Posts by Macropheliac

Re: Create A Modified Data Log With Before And After Values

Perhaps a shared workbook would suit you. You can track changes this way.

Mac

Re: Combining Different Sheets Into A New Sheet

Hi Eddy,
Can you post an example of your workbook? I'm sure this would be simple to do.

Mac

Re: Consolidating Data

Hi, everscern,
Do you want to move any row that is changed? Using the Change event will produce several duplicates if more than 1 cell in the row is changed. What is the range of columns your data is in?

Mac

Re: Transparent Worksheet On Open

Hi, nirmaludupa!
I'm not sure I follow you. What kind of automation problems are caused by hiding the sheet? What do you mean by transparent? Perhaps an attachment would help to better understand.

Mac

Re: Counting Macro Used

One way is to store it somewhere in a worksheet. Choose your location and add the following to your button code.

Range("A1").value=range("A1").value+1

Modify the range as needed.

Mac

Re: Validation Drop-down Arrow Not Showing

Bpoitras,
I think you have a corrupt workbook. None of the other things that I know may cause this are the cause. However, if you insert a new sheet and copy all the information via Select All, the arrows return. It worked when I tried it anyway. Try it and post back.

Mac

Re: Copy A Worksheet In Current Workbook

Just right-click on the sheet tab, select move or copy, check the create copy box.

Re: Multiple Named Ranges Reference Vba

You know, JimFiller1, sometimes the answer is so obvious, my eyes just won't see it.:oops: :oops:

Thank you very much,
Mac

Hello!

I wonder if someone could advise me.

I am using the following Selection_Change Event to show a UserForm when a cell in 1 of 31 named ranges is selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Dim i As Long


For i = 1 To 31
        If Not Intersect(Target, Range("StatPost" & i)) Is Nothing And Target.Value = "" Then
                If Target.Offset(0, -8).Value = "" Or Target.Offset(0, -7).Value = "" Or Target.Offset(0, -6).Value = "" Or Target.Offset(0, -5).Value = "" Or Target.Offset(0, -3).Value = "" Or Target.Offset(0, -2).Value = "" Or Target.Offset(0, -1).Value = "" Then
                        MsgBox "Please enter values in all required fields...", vbOKOnly, "User Error"
                        Target.Offset(0, -8).Activate
                Else
                        UserForm1.Show
                End If
        End If
Next i
End Sub

The code works fine. However, it must loop 31 times each time a cell is selected. I'm trying to find a better way. I tried the following, but there seems to be a limit of 24 ranges.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub


        If Not Intersect _
        (Target, Range("StatPost1,StatPost2,StatPost3,StatPost4,StatPost5,StatPost6,StatPost7,StatPost8,StatPost9,StatPost10,StatPost11,StatPost12,StatPost13,StatPost14,StatPost15,StatPost16,StatPost17,StatPost18,StatPost19,StatPost20,StatPost21,StatPost22,StatPost23,StatPost24,StatPost25,StatPost26,StstPost27,StatPost28,StatPost29,StatPost30,StatPost31")) Is Nothing And Target.Value = "" Then
                If Target.Offset(0, -8).Value = "" Or Target.Offset(0, -7).Value = "" Or Target.Offset(0, -6).Value = "" Or Target.Offset(0, -5).Value = "" Or Target.Offset(0, -3).Value = "" Or Target.Offset(0, -2).Value = "" Or Target.Offset(0, -1).Value = "" Then
                        MsgBox "Please enter values in all required fields...", vbOKOnly, "User Error"
                        Target.Offset(0, -8).Activate
                Else
                        UserForm1.Show
                End If
        End If
End Sub

I would appreciate any advice given.

Thanks,
Mac

Re: Move Cell One Column Right Offset Rows

Hi, jdbegg!

Does this do it?

Sub Cut_Paste()
Dim Tcell As Range
Dim MyCut As Range
Dim MyPaste As Range


For Each Tcell In Range("B:B,D:D")
        If Tcell.Value = "Discussion" Then
                Set MyCut = Tcell
                Set MyPaste = Tcell.Offset(-2, 1)
                MyCut.Copy MyPaste
                MyCut.ClearContents
                MyCut.EntireRow.Delete
        End If
Next Tcell
End Sub

Re: Increase Date By 1 Month & Return Month Name

Quote

Hmmm... Maybe you could have the spin button always pick the first day of the month and plug that into the first day cell. Then you could just write formulas for the other days (=A1+1), etc. You could then trigger a procedure to hide the last columns if they have a month number greater than the first cell.

Great idea, Jim!

Using your suggestion, I was able to accomplish the task with the following:

Sub Increase_Month()
Dim MyDate As Long


Select Case Month(Range("A1").Value)
        Case 1 To 11
                Range("A1").Value = MonthName(Month(Range("A1").Value) + 1) & " " & "1, " & Year(Range("A1").Value)
        Case 12
                Range("A1").Value = MonthName(Month(Range("A1").Value) - 11) & " " & "1, " & Year(Range("A1").Value) + 1
End Select


For MyDate = 1 To 31
        If Month(Range("Date" & MyDate).Value) > Month(Range("A1").Value) Then
                Range("Date" & MyDate).Resize(52).EntireRow.Hidden = True
        Else
                Range("Date" & MyDate).Resize(52).EntireRow.Hidden = False
        End If
Next MyDate
End Sub




Sub Decrease_Month()
Dim MyDate As Long


Select Case Month(Range("A1").Value)
        Case 2 To 12
                Range("A1").Value = MonthName(Month(Range("A1").Value) - 1) & " " & "1, " & Year(Range("A1").Value)
        Case 1
                Range("A1").Value = MonthName(Month(Range("A1").Value) + 11) & " " & "1, " & Year(Range("A1").Value) - 1
End Select


For MyDate = 1 To 31
        If Month(Range("Date" & MyDate).Value) > Month(Range("A1").Value) Then
                Range("Date" & MyDate).Resize(52).EntireRow.Hidden = True
        Else
                Range("Date" & MyDate).Resize(52).EntireRow.Hidden = False
        End If
Next MyDate


End Sub

After entering the formulas as you suggested, I simply had to code for A1 and add the hide procrdure, after. It works great.

Dave, I tried your suggestion as well. It returned the name of the month only. I do appreciate your help, though.

Many thanks,
Mac

Re: Increase Date By 1 Month

Quote

By the way, what will you do if the day is 31 and the next month doesn't have 31 days?

Hi JimFuller1! Thanks for responding.

Well, I have an entry sheet that is used to enter information for 1 month. The data is separated into daily sections. The date is at the top of each section. The user clicks a spinbutton to select the next month or previous month. With the selection, the dates must be increased or decreased by 1 month. I am currently working on a procedure to allow for all possibilities(December, 30, 31, 28, 29). In the mean time, I thought I'd ask if anyone knew a method to increase a date by 1 month. I know there are a lot of variables, but maybe there's some method that I don't know.

Thanks for your help. I do appreciate it.
Mac

Hello!

I'm looking for a better way to increase a date by 1 month, using VBA.

I can use the following, but not without numerous Select Case or If statements to take into account the varying month lengths or whether or not the date in question is December.

Range("A1") = MonthName(Month(Range("A1")) + 1) & " " & Day(Range("A1")) & ", " & Year(Range("A1"))

Any better ideas or suggestions are welcomed and appreciated.

Thanks,
Mac

Re: Textbox Time Calculation

Bryce and Squeegedog,

Thank you both for your help. Both suggestions worked brilliantly. Believe it or not, I've been trying to find the proper syntax for three days.:?

Thanks,
Mac

Hello all!

After numerous searches and attempts, I am at a loss. I'm attempting to calculate the total number of hours between a start time and end time using textboxes on a Userform.

Here is my latest attempt:

Private Sub D24_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim myvalue As String
Dim myhour As String
Dim myminutes As String


myvalue = D24.Value


Select Case Len(D24)
        Case 1
                myhour = "0" & Mid(myvalue, 1, 1)
                myminutes = "00"
        Case 2
                myhour = Mid(myvalue, 1, 2)
                myminutes = "00"
        Case 3
                myhour = Mid(myvalue, 1, 1)
                myminutes = Mid(myvalue, 2, 2)
        Case 4
                myhour = Mid(myvalue, 1, 2)
                myminutes = Mid(myvalue, 3, 2)
End Select
D24.Text = myhour & ":" & myminutes


If D24.Text <> "" And D25.Text <> "" Then D27.Text = TimeValue(D25.Value) - TimeValue(D24.Value)
End Sub


 


Private Sub D25_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim myvalue As String
Dim myhour As String
Dim myminutes As String


myvalue = D25.Value


Select Case Len(D25)
        Case 1
                myhour = "0" & Mid(myvalue, 1, 1)
                myminutes = "00"
        Case 2
                myhour = Mid(myvalue, 1, 2)
                myminutes = "00"
        Case 3
                myhour = Mid(myvalue, 1, 1)
                myminutes = Mid(myvalue, 2, 2)
        Case 4
                myhour = Mid(myvalue, 1, 2)
                myminutes = Mid(myvalue, 3, 2)
End Select
D25.Text = myhour & ":" & myminutes


If D24.Text <> "" And D25.Text <> "" Then D27.Text = TimeValue(D25.Value) - TimeValue(D24.Value)
End Sub

The above codes are an attempt to force an entry as time (7, 07, 700, 0700=7:00 / 13, 1300=13:00 etc.). However, the result is not correct.

For example:
Start Time in Textbox(D24)=07:00
End Time in Textbox(D25)=17:30
Result in Textbox(D27)=0.4375 :flame:
Desired Result in Textbox(D27)=10.5

I've never calculated times in textboxes, so I'm stumped.:confused:

I would be more than grateful if someone provide a suggestion as to how to accomplish this. I have attached an example.

Thanks,
Mac

Re: Workbook Protection When Macros Are Not Allowed

You could make your worksheets hidden all the time. Insert a dummy sheet into your workbook. Make the dummy sheet the only sheet visible. In your Workbook_Open code, make the dummy sheet hidden and the desired sheet visible. Therefore, if macros are disabled, the only sheet visible will be the dummy sheet.

Mac

Re: Unlock a cell acording to another cell

Mistaken Thread

Re: Saveas Dialog

This might do it:

Sub SaveBttn_Click()
Dim NewName As String
Application.DisplayAlerts = False
Sheets(Array("Auction", "Cashier", "Silent", "LIVE", "FineArt", "Jewelry", "Donated")).Copy


If MsgBox("Would you like to save?", vbYesNo) = vbNo Then
   ActiveWorkbook.Close False
Else
   NewName = Application.GetSaveAsFilename
   If NewName = "False" Then Exit Sub
   ActiveWorkbook.SaveAs NewName
End If


Application.DisplayAlerts = True
End Sub

Let me know how it works out.

Mac

Re: Saveas Dialog

Will the file always be saved to the same location as the workbook from which the code is run? If not, how would you like to choose the location?

Re: Add 1 To An Existent Number In A Cell By Clicking Another Cell

You've lost me here. What do you mean by:

Quote

multiples of this on the same sheet.