Posts by h

Sorry Roy, i did not make my self clear

Is there a code to Disable the manue that appears when
Placing mouse on a sheet Tag and then press mouse wright click ?
I tried This one:......but

Application.CommandBars("sheet").Enabled = False

My Second Problem:
I have the comming code........is there a way to put the name of a closed WB insted of the active workbook wich its name is (oo.xls)
i.e.insted of this opened WB.
there might be a way to choose any closed WB.

Sheets("OrderForm").Copy After:=Workbooks("oo.xls").Sheets(3)

Thank u

Hi

Is there a code to Disable the manue that appears when pressing mouse wright click on a sheet ?
I tried This one:......but

Application.CommandBars("sheet").Enabled = False

also, In this code........is there a way to put the name of a closed WB insted of the active workbook name(oo.xls) i.e.insted of the opened WB.
there might be a way to choose any closed WB.

Sheets("OrderForm").Copy After:=Workbooks("oo.xls").Sheets(3)

Thank u

Yes I want a VBA Code to do the job......

Thank u

Hi

I have an active WB1.......
I can save sheet1(or any sheet) to a different WB (WB name:Carpet)........

Now The active file is WB2....
I want to save sheet1(or any sheet) to the WB(Carpet)

I know how to save a sheet to a different WB, but the problem is saving another sheet(from the same active file or another file) to the same WB

Could any u help me doing this job?

yours
h

Thank u Dave

The perfectely works..................

Hi
i h ave a vbModeless userform activated on the excel sheet....

is there a code to swithch to excel window and back to userform ?

thanks

Hi

I have a combobox that shows several names.....

the point i want is when i select a name from drop-down combobox, so the chosen name should work as a hyperlink to other cell within the same sheet

i.e. when choosing a name, it should take me to a preselected cell
names in combobox shoul be hyperlinked to other cells within the same sheet.

hope it is clear

yours
h

Hi

I have a WB1 that is linked(via lots of formulas) to WB2........

I did unchek AskTo Update Links in the WB1 to avoid being asked to edit Links , I also within the Edit link dialoge box i checked Donot ask to update linke and update links..............

my problem is: even did what i discribed above i still receive Edit Link dialoge box when opening WB1...i confirmed that all the links are correct.....

i do not want to receive this dialoge box each time i open WB1

Thank u 4 your help
yours
h

ok here is the xls fiel where i found the code that requires such reference

pls find the attached file

after doing what Dave said, set Cancel property of the bottom to true...

in this case Esc well work...i.e while u filling in a userform and suddenlly want to stop working with the userform..then press Esc botton

it will work without any need to go to cancel bottom on your userform

yours
h

Hi
I cannot find this reference in VBA Reference
Dim ALT As Alert.Alerter

any idea where to find it

Thank u

Hi

if i am filling in a userform and suddenlly decide to Esc,
is there a code to do this?

is there a code to move the cursor from a textbox to the Load bottom without having to pass all the textboxes ?

yours
h

Thank u all

my problem has been solved

here is the complete code

Sub MyCalc()
    Dim dblTot As Double
    Dim dblConsu As Double
    Dim dblMaint As Double
    Dim dblClea As Double
    Dim dblcarmaint As Double
    Dim dblTransM As Double
    Dim dblmachm As Double
    Dim dblbuilm As Double
    Dim dblDelv As Double
    Dim dblDtax As Double
    Dim dbloilful As Double
    Dim dblelec As Double
    Dim dblbouns As Double
    Dim dblstatio As Double
    Dim dbltaxes As Double
    Dim dblpress As Double
    Dim dblservic As Double
    Dim dblcommesio As Double
    Dim dblfirinsu As Double
    Dim dblregs As Double
    Dim dblcarinsu As Double
    Dim dblgust As Double
    Dim dblels As Double
    Dim dblels2 As Double


    If IsNumeric(tot.Text) Then dblTot = CDbl(tot.Text)
    If IsNumeric(txtConsu.Text) Then dblConsu = CDbl(txtConsu.Text)
    If IsNumeric(txtMaint.Text) Then dblMaint = CDbl(txtMaint.Text)
    If IsNumeric(txtClea.Text) Then dblClea = CDbl(txtClea.Text)
    If IsNumeric(carmaint.Text) Then dblcarmaint = CDbl(carmaint.Text)
    If IsNumeric(TransM.Text) Then dblTransM = CDbl(TransM.Text)
    If IsNumeric(machm.Text) Then dblmachm = CDbl(machm.Text)
    If IsNumeric(builm.Text) Then dblbuilm = CDbl(builm.Text)
    If IsNumeric(Delv.Text) Then dblDelv = CDbl(Delv.Text)
    If IsNumeric(Dtax.Text) Then dblDtax = CDbl(Dtax.Text)
    If IsNumeric(oilful.Text) Then dbloilful = CDbl(oilful.Text)
    If IsNumeric(elec.Text) Then dblelec = CDbl(elec.Text)
    If IsNumeric(bouns.Text) Then dblbouns = CDbl(bouns.Text)
    If IsNumeric(statio.Text) Then dblstatio = CDbl(statio.Text)
    If IsNumeric(taxes.Text) Then dbltaxes = CDbl(taxes.Text)
    If IsNumeric(press.Text) Then dblpress = CDbl(press.Text)
    If IsNumeric(servic.Text) Then dblservic = CDbl(servic.Text)
    If IsNumeric(commesio.Text) Then dblcommesio = CDbl(commesio.Text)
    If IsNumeric(firinsu.Text) Then dblfirinsu = CDbl(firinsu.Text)
    If IsNumeric(regs.Text) Then dblregs = CDbl(regs.Text)
    If IsNumeric(carinsu.Text) Then dblcarinsu = CDbl(carinsu.Text)
    If IsNumeric(gust.Text) Then dblgust = CDbl(gust.Text)
    If IsNumeric(els.Text) Then dblels = CDbl(els.Text)
    If IsNumeric(els2.Text) Then dblels2 = CDbl(els2.Text)


    ' load text values into variables
    ' variables with invalid text will be zero
    bal.Value = dblTot - (dblConsu + dblMaint + dblClea _
                        + dblcarmaint + dblTransM + dblmachm + dblbuilm _
                        + dblDelv + dblDtax + dbloilful + dblelec + dblbouns + _
                          dblstatio + dbltaxes + dblpress + dblservic _
                        + dblcommesio + dblfirinsu + dblregs + dblcarinsu + _
                          dblgust + dblels + dblels2)




End Sub
Private Sub tot_Change()
    MyCalc
End Sub

yours
h

Welcome Ranto in this Fantastic Excel Web

you can use this web search engine to find out your pice of information
you can find almost all what you need here in the site

it is one of the best to start with.............. using Excel

I finally recommend u to use Excel 2003 as i used Excel 97,2000,XP and still using all of them due to my work nature (Chartered accountant moving to client bases)..........I have found that Excel2003 provide the dynamic help, specially you said that you are going to use it to trac stock movment, so 2003 version does provide such tool for this kind of job.......

Finally i wish u all the best
yours
h

Hi

I have several Textboxs in a userform
main Textbox shows the total of the values entered into textboxs

my problem is : the total does not appear in the main Textbox untill i fill in
all other Textboxs............somtimes i want to fill in only few textboxes, so
the total does not appear in the Textbox that shows the total......

here is the codes i used in the userform:

Sub MyCalc()
If IsNumeric(tot.Text) Then
If IsNumeric(txtConsu.Text) Then
If IsNumeric(txtMaint.Text) Then
If IsNumeric(txtClea.Text) Then
If IsNumeric(carmaint.Text) Then
If IsNumeric(TransM.Text) Then
If IsNumeric(machm.Text) Then  


bal.Value = CDbl(tot.Value) - (CDbl(txtConsu.Value) _
+ CDbl(txtMaint.Value) + CDbl(txtClea.Value) _
+ CDbl(carmaint.Value) + CDbl(TransM.Value) _
+ CDbl(machm.Value))
End If
End If
End If
End If
End If
End If
End If
END Sub


Private Sub tot_Change()
    MyCalc
End Sub
Private Sub txtConsu_Change()
    MyCalc
End Sub
Private Sub txtMaint_Change()
    MyCalc
End Sub


Private Sub txtClea_Change()
    MyCalc
End Sub


Private Sub carmaint_Change()
    MyCalc
End Sub


Private Sub TransM_Change()
    MyCalc
End Sub


Private Sub machm_Change()
    MyCalc
End Sub

I highly appreciate any help
yours
h

Thank u all

My problem is solved and here is the code that has worked well:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim T As Range
    Dim bal As Variant
    Set T = Range("I5:BN1000")
    bal = Range("F" & Target.Row).Value


    If Not Application.Intersect(Target, T) Is Nothing Then
        If bal <> "" And _
           bal <> 0 Then


            Beep
            Msg = MsgBox(" imbalanced " & bal, vbCritical, "The Balance!")
        End If
    End If
Edn Sub

ok, thanks 4 reply

i tried set bal...but nothing changed

the problem is till there

hi

my problem is with bal.value, a message box appears tilling me object variable or with block variable not set............the rest of hte code works fine

could u pls tell me why is this..........Thank u

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim T As Range
    Dim bal As Range
    Set T = Range("I5:BN1000")
    bal.Value = Range("F" & Target.Row)


    If Not Application.Intersect(Target, T) Is Nothing Then
        If ActiveSheet.Range("F" & Target.Row).Value <> "" And _
           ActiveSheet.Range("F" & Target.Row).Value <> 0 Then


            Beep
            MsgBox "imbalanced" & bal
        End If
    End If
End Sub

all helps r appreciated
yours
h

sorry i did not make my self clear

what i mean: i want the target to be A cell that sum the integer in B and integer in C, so when i enter 20 in B1 and 20 in C1 then A1 changes to 0, so i want the A1 is target ...........and so on with A2 A3

Hi
A1 B1 C1
0 20 20

A1:=sum(B1-C1)

i want the code to recognise A1 as the Target, so if the Target <> 0 then procced the rest

Quote

Code

[code=php]Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim T As Range
    Set T = Range("A5:A1000")
    If Application.Intersect(Target, T) Is Nothing Then
        If Target.Value <> 0 Then
            Beep
            Msg = MsgBox("   Imbalanced operation with  " & Target.Value, vbCritical, "The Balance!")
        End If
    End If
   
End Sub

Display More

[/code]

could someone pls help........any help is appreciated

yours
h