Posts by JJacob

Re: Listbox.list Generated From Multi Columns?

Thank you Andy, as usual it works perfectly

This code only adds Item from first column to list. Is their a reason that A listbox.list cannot be generated from a single row with multi columns? "N" is showing the correct integer to list data from the appropriate columns into the listbox. Again it only adds first item (data in first column).

thanks

Set notes = Range(wb.Worksheets("2007").Cells(r, 51), wb.Worksheets("2007").Cells(r, 61))
N = 51
For Each cnotes In notes
If Trim(cnotes.Value) <> "" Then
N = N + 1
End If
Next cnotes
MsgBox ("n=" & N)
ListBox1.Font.Size = 10
ListBox1.Font.Bold = True
ListBox1.ColumnCount = 1
ListBox1.List = Range(wb.Worksheets("2007").Cells(r, 51), wb.Worksheets("2007").Cells(r, N)).Value

Re: Loop To Paste To First Blank Cell In Range

Thanks for the help. Trim was the problem.

This loop is overwriting the first cell in range and not writing to the first blank cell in range.

For Each cnote In NOTE
If Trim(cnote).Value = "" Then
cnote.Value = notes.Value
Exit For
Else
End If
Next cnote

If I take out the exit for , it copies to all cells in range

thanks

Re: Set Offset Range For Loop

thank you Andy.I have spent the last two hours trying to figure that out.

I am trying to loop through a range and add data to the first empty cell in the range. This does not seem to be working.

Set NOTE = Range("rStartCell.Offset(0, 50):rStartCell.Offset(0, 60)")
For Each cnote In NOTE
If cnote.Value < 0 Or cnote.Value = "" Then
cnote.Value = notes.Value
Exit For
Else
End If
Next cnote

thanks for the help

Re: Trigger Macro When Cell Withing Range Deleted

Thank you for your help Mavyak, I think this will do perfectly!

Re: Trigger Macro When Cell Withing Range Deleted

Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim r As Range 
    Set r = Intersect(Target, Range("D10:F37")) 
    If Not r Is Nothing Then 
        MsgBox r.Address 
    End If 
End Sub

This code is also triggering macro as I add data not just when removing from the worksheet range and is causing problems. any way to make it work only when cell are cleared or deleted in a specified range?

thanks

What is the best way to trigger a macro to run when cells are deleted with either right click or keyboard in a specific range on a worksheet?

thanks

Re: Moveafterreturndirection Xltoright In Specified Range

Thank you Kenneth,

I added this

If Not Intersect(Target, Range("Goback")) Is Nothing Then
       ActiveCell.Offset(1, -5).Select
    End If

Works like a carriage return on a typewritter.

thanks again

Re: Moveafterreturndirection Xltoright In Specified Range

It wont allow address on right side and If I move address off left, it does nothing

thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = ThisWorkbook.Worksheets("mastersheet").Range("trgadd") Then
Application.MoveAfterReturnDirection = xlToRight
Else
Application.MoveAfterReturnDirection = xlDown
End If

Can't get this to work

thanks

Re: Keep Userform On Top In Windows?

[h1][st]PROBLEM SOLVED,THANKS FOR THE HELP[/st][/h1]

Found the answer here:

http://www.xcelfiles.com/API_06.html

Also right click paste to textbox here:

http://word.mvps.org/FAQS/Userforms/AddRightClickMenu.htm

Now I can keep a userform visible in any desktop and right click copy from one source and right click paste directly to a userform textbox without having to re-enter data.

Re: Keep Userform On Top In Windows?

Hi Andy,

thanks for the help.

Mr Maolas Splash screen examples fall behind an application that is opened if selected after the splashscrrens have intitialized. His Calculator "stay on top" program leaves the calculator in front of any application desktop either already running or selected after the calculator application is started.

I am trying to get a userform to stay ln front of a mainframe database desktop so that data can be copied line by line and entered into textboxes for calculations. If I try it now, as soon as the data to be copied is selected, the userform drops behind the data base desk top and then the database desktop must be minimized to copy into the userform.

thanks for the help

Is it possible to keep an excel userform on top of an active window from an application other than excel? I have seen a program using API calls from Ivan Maola forcing windows calculator application to remain on top of any desktop and I am curious if it can be done with a userform.

thanks

Re: Right Click Paste To Textbox

This code is working from any source I have tried so far using the mousedown event.

Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Set doclip = New DataObject
doclip.GetFromClipboard
scode = doclip.GetText
scode = Replace$(scode, Chr$(145), Chr$(39))
scode = Replace$(scode, Chr$(147), Chr$(34))
scode = Replace$(scode, Chr$(148), Chr$(34))
doclip.SetText scode
doclip.PutInClipboard
scode = doclip.GetText
TextBox1 = scode
End Sub

I am also using the code below to hide excel application while I am copying from the outside source while I am pasteing to the textbox.

Private Sub UserForm_Click()
Application.Visible = False


End Sub

As soon as I select the data from an outside source to copy, the userform drops behind the copy source. I then need to minimize the copy source to have access to the textbox to paste. It drops out of sight whether shown modal or not. Any Ideas how to keep it at the front of the desktop without minimizing the copy source?

thanks

Re: Right Click Paste To Textbox

Thanks for the help RoyUK but I am needing to paste from a mainframe not a workbook. Think it will still work? I am not currently on the desktop to log in and try it.

thanks

Re: For Loop Goes Into An Infinite Loop

Are you maybe not telling the code to exit the loop at some point?

Is it possible to copy data and right click paste it into a textbox on a userform?

thanks

Re: Application.worksheetfunction.sum With Variable

Badher,
Thank you for your help, it was a variable issue.
thanks again